Forum Discussion

Copper Contributor

Feb 02, 2024

Create rows from a numeric and text list

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 s...

excel

Formulas and Functions

PeterBartholomew1

Silver Contributor

Feb 02, 2024

Ethan_leb

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 & " " & text, 3)
  )

SergeiBaklan

Diamond Contributor

Feb 03, 2024

PeterBartholomew1

Looks nice.

Couple of more variants

=LET(
    abc, MID(mixedList, SEQUENCE(LEN(mixedList)), 1),
    InsertDelimeter, LAMBDA(a,v,
        a & IF(ISNUMBER(--v) * (RIGHT(a) = " "), "|", "") &  v
        ),
    ConcatBack, REDUCE("", abc, InsertDelimeter),
    TEXTSPLIT(ConcatBack, , "|")
)

=LET(
    split, TEXTSPLIT(mixedList, , " " & SEQUENCE(9)),
    position, SEARCH(split, mixedList) - 1,
    firstChar, IFERROR(MID(mixedList, position, 1), ""),
    firstChar & split
)

etc.