Create rows from a numeric and text list

Question

I need a formula thats able to make a new row every time a new group of numbers appears

Ex. 4485 NI 4487 4491 6829 ADO 9152 CB

Numbers are randomly grouped and some will have text some wont so they will look like

4485 NI

4487

4491

6829 ADO

9152 CB

thank you!

peterbartholomew1 · Answer

Ethan_leb&nbsp;This is a 365 solution (I have long forgotten traditional Excel)= LET(
    groups,  TEXTSPLIT(mixedList, , " "),
    istext?, ISERROR(VALUE(groups)),
    numbers, IF(istext?, NA(), groups),
    text,    VSTACK(DROP(IF(istext?, groups, ""),1), ""),
    TOCOL(numbers &amp; " " &amp; text, 3)
  )&nbsp;

sergeibaklan · Answer

PeterBartholomew1&nbsp;
Looks nice.
Couple of more variants
=LET(
    abc, MID(mixedList, SEQUENCE(LEN(mixedList)), 1),
    InsertDelimeter, LAMBDA(a,v,
        a &amp; IF(ISNUMBER(--v) * (RIGHT(a) = " "), "|", "") &amp;  v
        ),
    ConcatBack, REDUCE("", abc, InsertDelimeter),
    TEXTSPLIT(ConcatBack, , "|")
)
or
=LET(
    split, TEXTSPLIT(mixedList, , " " &amp; SEQUENCE(9)),
    position, SEARCH(split, mixedList) - 1,
    firstChar, IFERROR(MID(mixedList, position, 1), ""),
    firstChar &amp; split
)
etc.

Forum Discussion

Create rows from a numeric and text list

2 Replies