Link to home
Start Free TrialLog in
Avatar of Anthony Mellor
Anthony MellorFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Substitute every 4th instance of character in string? Lambda/Let iteration?

Substitute every 4th instance of character in string?

So replace every 4th comma with ; and just to spice it up a bit, make that replace with ";" (replace comma with "quotes semi colon quotes") . Does that need an escape or more quotes, I tried and failed?

SUBSTITUTE(text, old_text, new_text, [instance_num])
It's such a shame [instance_num] provides only one instance. Google doesn't know.

How?

Thought it would be easy, but it seems not.
Now I am thinking Lambda/Let iteration?

No VBA split thanks.

Anthony
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

This formula will handle up to 6 substitutions or a total of 27 commas. If more commas, add more SUBSTITUTE function calls.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,",",""";""",4),",",""";""",7),",",""";""",10),",",""";""",13),",",""";""",16),",",""";""",19)

Kevin
Avatar of Anthony Mellor

ASKER

Ah, there's a limitless list. I was thinking of one of your iterations?

Anthony
With the LAMBDA? It would work but it would also have limitations - remember the stack limit? It's possible but do you really need it? How long (how many commas) are in these strings?

Kevin
oh yes.
Say 3,000 x 6 = 18,000
Presently though maybe 300 x 3 = 900

I may have a way to avoid the need for the substitution in the first place. Need to sleep on it (literally).

Anthony
ASKER CERTIFIED SOLUTION
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You can also try

=TEXTJOIN(""";""",TRUE,IFERROR(LET(lft,FIND("#",SUBSTITUTE(","&A2,",","#",ROW(A1:A29)*4-4+1)),len,FIND("#",SUBSTITUTE(","&A2&",,,,",",","#",ROW(A1:A29)*4+1))-lft,MID(A2,lft,len-1)),""))

You would have to extend the range A1:A29 for more replacements than 29.
no one gets paid enough ;-)

"And don't ask me how it works. I've already forgotten."

THAT is my primary problem with Lambdas, I write one today and next week I can't figure out how it works.

Anthony

p.s thanks Saqib
There was a bug or two in my formula. Here is a refined version:

=LET(Count,ROUNDDOWN((LEN(A1)-LEN(SUBSTITUTE(A1,",","")))/4+1,0),Start,FIND("|",SUBSTITUTE(","&A1,",","|",SEQUENCE(Count,,1,4))),Length,FIND("|",SUBSTITUTE(A1&",,,,",",","|",SEQUENCE(Count,,4,4)))-Start,TEXTJOIN(""";""",,MID(A1,Start,Length)))

Kevin
there was? Seemed to work ok here - will put in your update now. Thanks