Forum Discussion
REGEX+TEXTSPLIT
Dear Experts,
I have a data like below in column"A":-
And , I want to split it in the way like in "Yellow" So, after each consecutive Alphabet or number it should be splitted, I tried to do something in Column "B" but not succesful. Could you please share any solution if possible by using a comb of regex + textsplit?
Thanks in Advance,
Br,
Anupam
Hi anupambit1797 ,
On the shoulders of giants... or in this case m_tarler 's shoulders.
Try:
=TEXTSPLIT(LET(a,REGEXEXTRACT(A2,"([A-Z0-9])",1,1),CONCAT(a&IF(a=HSTACK(DROP(a,,1),""),",",""))),",")to get what you want - at least what I THINK you want
6 Replies
If without regex we may use
RepSplit=LAMBDA(txt, LET( sep, "|", engine, LAMBDA(fn,str, IF(LEN(str) <= 1, str, LET( h, LEFT(str), t, RIGHT(str, LEN(str) - 1), h & IF(h=LEFT(t), sep, "") & fn(fn,t) )) ), rs, LAMBDA(str, engine(engine, str)), TEXTSPLIT( rs(txt), sep ) ) );With Regex we may use peiyezhu pattern inside Excel
=TEXTSPLIT(REGEXREPLACE(A2, "(.)(?=\1)", "\1|"), "|")Results are
Hi anupambit1797 ,
On the shoulders of giants... or in this case m_tarler 's shoulders.
Try:
=TEXTSPLIT(LET(a,REGEXEXTRACT(A2,"([A-Z0-9])",1,1),CONCAT(a&IF(a=HSTACK(DROP(a,,1),""),",",""))),",")to get what you want - at least what I THINK you want
select *,regreplace('(.)(?=\1)','\1,',id) from Sheet2;
I'm not good with REGEX so this is probably more complicated than needed but seems to work:
=LET(a,REGEXEXTRACT(A2,"([A-Z0-9])",1,1),CONCAT(a&IF(a=HSTACK(DROP(a,,1),""),",","")))
