Search code examples
excelexcel-formuladata-manipulation

Separate numbers and text in Excel

Is there an Excel formula I can write in Excel to separate a combination of numbers and text from a cell?

col A is how the data is formatted, cols b - i are how I need them to be:

col A col b col c col d col e col f col g col h col i
1EA/1PK/16BX/124CA 1 EA 1 PK 16 BX 124 CA
1EA/6CA 1 EA 6 CS
Solution

  • Assuming you have two trailing letters '[A-Z]' in each concatenated value, have a go with:

    enter image description here

    Formula in B1:

    =DROP(IFERROR(REDUCE(0,A1:A2,LAMBDA(a,b,VSTACK(a,LET(c,TEXTSPLIT(b,"/"),TEXTSPLIT(TEXTJOIN("|",,REPLACE(c,LEN(c)-1,0,"|")),"|"))))),""),1)

    To do this irrespective of the amount of trailing characters after digits:

    =DROP(IFERROR(REDUCE(0,A1:A2,LAMBDA(a,b,VSTACK(a,LET(c,TEXTSPLIT(b,ROW(1:10)-1,,1),d,SUBSTITUTE(TOROW(WRAPCOLS(HSTACK(TEXTSPLIT(b,c,,1),c),COUNTA(c))),"/",),IFERROR(--d,d))))),""),1)

    Idea here is to delimit each input in recursion by any digit. The remainder could then be input as an array to delimit the input by to retrieve all numbers. Some other functions will then shuffle around both these arrays to VSTACK() properly to our previous rows of the output.