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
Last Comment
Anthony Mellor
ASKER
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
ASKER
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
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
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.
ASKER
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
ASKER
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