Separate numbers and text in Excel

Question

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

JvdV · Accepted Answer · 2022-11-04T22:41:37.057

3

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.

edited Nov 04 '22 at 22:41

answered Nov 04 '22 at 14:53

JvdV

70,606
8
39
70

Nice how you can get the textsplit to spill down. See my reply. Would you know a way to apply that? – P.b Nov 04 '22 at 15:53
@p.b thanks. I'm not behind a pc now but will have a look this weekend! – JvdV Nov 04 '22 at 17:35
1

@P.b, added an option to do this irrespective of length of trailing characters. Note that this is then also does not rely on `TEXTJOIN()` and it's possible limits of concatenating the entire input first. It's basically a recursive function to take a single input and `VSTACK()` it to the previous rows of the output. – JvdV Nov 04 '22 at 22:43
Very nice. So REDUCE behaves kind of like SCAN/BYROW? – P.b Nov 05 '22 at 08:05
1

@P.b, yes that's a good way to describe this behaviour. A very nice alternative to the limits of TEXTSPLIT, see [here](https://stackoverflow.com/a/73853318/9758194). Usefull in more instances where one would want to look back at previous iterations too. – JvdV Nov 05 '22 at 08:15
1

Found more use for it already https://stackoverflow.com/a/74326770/12634230 – P.b Nov 05 '22 at 10:02

P.b · Answer 2 · 2022-11-04T17:06:14.447

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

Separate numbers and text in Excel

2 Answers2

Linked