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

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

Microsoft ExcelMicrosoft Office

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

Anthony

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

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

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

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

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.

=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

"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

=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

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

Kevin