# Create rows from a numeric and text list

Copper Contributor

# 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 so they will look like

4485 NI

4487

4491

9152 CB

thank you!

2 Replies

# Re: Create rows from a numeric and text list

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)
)``````

# Re: Create rows from a numeric and text list

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.