Forum Discussion

Ethan_leb's avatar
Ethan_leb
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's avatar
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's avatar
    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, , "|")
)

    or

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

    etc.