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:

CodePudding user response：

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

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.

CodePudding user response：

I have a solution that would spill the result, numbers are numbers and text (or number) length can be variable:

=LET(number,TEXTSPLIT(A1,CHAR(SEQUENCE(1,26,65)),,1),
     text,TEXTSPLIT(A1,SEQUENCE(1,10,0),,1),
     spill,TOROW(VSTACK(number,text),,1),
     remove,SUBSTITUTE(spill,"/",""),
IFERROR(--remove,remove))

number splits the text by any capital letter. text splits the text by any number. spill is the result, but numbers are still text. This is fixed with remove

Mine doesn't spill by row and needs dragged. I think JvdV may know how to fix that.

Edit to spill down also:

=LET(data,A1:A2, 
     join,TEXTJOIN("|",,data), 
          number,TEXTSPLIT(join,CHAR(SEQUENCE(1,26,65)),,1), 
          text,TEXTSPLIT(join,SEQUENCE(1,10,0),,1), 
          spill,TOROW(VSTACK(number,text),,1), 
          spilldown,IFERROR(TEXTSPLIT(TEXTJOIN("•",1,spill),"•","|",1),""), 
          remove,SUBSTITUTE(spilldown,"/",""),
IFERROR(--remove,remove))