Extract all numbers from 1 cell to many cells-CodePudding

I have a field with numbers separated by two different characters. I am attempting to split these numbers in to their own field.

Current Data Example:

A
----
25-29
18-20,22,2
3,6
5,16-19
2-5,9-12
1,2,3,4

Expected Results

B-E would handle numbers on either side of "-"

F-I would handle any possible numbers by themselves

B     |  C     |  D     |  E     |  F     |  G     |  H     |  I 
----------------------------------------------------------------------
25       29
18       20                         22       2
                                    3        6
16       19                         5
2        5        9        12   
                                    1        2        3        4

I have tried using different combinations of MID LEFT FIND and I can't seem to figure this out. I know all 8 columns need a different formula but I am having the hardest time getting this figured out.

Any help on this is soo far beyond appreciated.

Thank you

CodePudding user response：

With the newest ms365's BETA-functions, you could try:

Formula in B1:

=LET(X,TEXTSPLIT(SUBSTITUTE(A1,"-","*"),","),Y,EXPAND(TEXTSPLIT(TEXTJOIN("*",,FILTER(X,ISERROR(--X),"*")),"*"),,4,""),Z,FILTER(X,ISNUMBER(--X),""),HSTACK(Y,Z))

For those without TEXTSPLIT() but with office365:

=LET(str,"<t><s>"&SUBSTITUTE(SUBSTITUTE(A1,"-","*")&",a*a,a*a",",","</s><s>")&"</s></t>",X,FILTERXML(str,"//s[.*0!=0][position()<3]"),Y,IFERROR(FILTERXML(str,"//s[.*0=0]"),""),SUBSTITUTE(TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(TEXTJOIN("*",,X,Y),"*","</s><s>")&"</s></t>","//s")),"a",""))

For those without ms365, but with Excel 2019, they can use this CSE-formula and start dragging:

=IFERROR(SUBSTITUTE(FILTERXML("<t><s>"&SUBSTITUTE(TEXTJOIN("*",,FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE($A1,"-","*")&",a*a,a*a",",","</s><s>")&"</s></t>","//s[.*0!=0][position()<3]"),IFERROR(FILTERXML("<t><s>"&SUBSTITUTE($A1,",","</s><s>")&"</s></t>","//s[.*0=0]"),"")),"*","</s><s>")&"</s></t>","//s["&COLUMN(A1)&"]"),"a",""),"")