Forum Discussion

anupambit1797's avatar
anupambit1797
Iron Contributor
Sep 16, 2025
Solved

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 t...
BI & Data Analysis
excel
Formulas and Functions
  • RedNectar's avatar
    RedNectar
    Sep 17, 2025

    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

     

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Sep 18, 2025

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

Resources