Extract values from one cell into many cells

[JohnZ1156]

I have a series of numbers separated by " - ".
I'd like formulas to extrtact each number to separate cells.

EXAMPLE:

 

[Joe4]

You could do a Find/Replace, replacing all spaces with nothing to first get rid of all your spaces.
Then you could select that column, go to the Data menu, select Text to Columns, choose the "Delimited" option, enter a dash in the "Other" option under Delimiters, and click Finish.

This will split that one column into multiple columns.

 

[myall_blues]

You could use

=VALUE(TEXTSPLIT(B2," - "))

in C2 and copy it down

 

[Fluff]

I would go with text to columns, but a formula for 2021

=LET(a,"-"&B2&"-",s,SEQUENCE(,LEN(a)-LEN(SUBSTITUTE(a,"-",""))-1),--REPLACE(LEFT(a,FIND("^",SUBSTITUTE(a,"-","^",s+1))-1),1,FIND("^",SUBSTITUTE(a,"-","^",s)),""))

 

[felixstraube]

If you want to do it by formula you could use this:

Book1
	A	B	C	D	E	F	G
1
2		34 - 55 - 59 - 65 - 70	34	55	59	65	70
3		12 - 32 -38 - 40 - 57	12	32	38	40	57
4		23-29-38-61-70	23	29	38	61	70
5		6 - 7 - 24 - 44 - 54	6	7	24	44	54
Sheet3

Cell Formulas
Range	Formula
C2:G5	C2	=LET(t, "-"&B2&"-", s, SEQUENCE(,5), dt, SUBSTITUTE(SUBSTITUTE(t, "-","s", s), "-", "e", s), start, FIND("s", dt), end, FIND("e", dt), VALUE(MID(dt, start+1, end-start-1)) )
Dynamic array formulas.

 

[Joe4]

You could use

=VALUE(TEXTSPLIT(B2," - "))

in C2 and copy it down

According to Microsoft, the TEXTSPLIT function is not available in Excel 2021, which is the version they are shown as using.
So if that is indeed the case, they probably do not have this function at their disposal.

 

[Cubist]

You can try this for formula option:
Edit: Disregard TEXTAFTER and TEXTBEFORE not available in 2021.

Book1
	B	C	D	E	F	G
1		First	Second	Third	Fourth	Fifth
2	2 - 14 - 65 - 53 - 25	2	14	65	53	25
3	34 - 32 - 38 - 40 - 57	34	32	38	40	57
Sheet1

Cell Formulas
Range	Formula
C2:C3	C2	=--TEXTBEFORE(B2," - ")
D2:D3	D2	=--TEXTAFTER(TEXTBEFORE($B2 & " - "," - ",2),C2 & " - ")
E2:E3	E2	=--TEXTAFTER(TEXTBEFORE($B2 & " - "," - ",3),D2 & " - ")
F2:F3	F2	=--TEXTAFTER(TEXTBEFORE($B2 & " - "," - ",4),E2 & " - ")
G2:G3	G2	=--TEXTAFTER(TEXTBEFORE($B2 & " - "," - ",5),F2 & " - ")

 

[myall_blues]

According to Microsoft, the TEXTSPLIT function is not available in Excel 2021, which is the version they are shown as using.
So if that is indeed the case, they probably do not have this function at their disposal.

Thanks @Joe4 - I do wish there was a little consistency.

 

[Fluff]

Another option

=TRANSPOSE(FILTERXML("<k><m>"&SUBSTITUTE(B2,"-","</m><m>")&"</m></k>","//m"))

 

[FormR]

And another way using Rick's method from way back in 2012.

Book2
	A	B	C	D	E	F	G
1			First	Second	Third	Fourth	Fifth
2		2 - 14 - 65 - 53 - 25	2	14	65	53	25
3		34 - 32 - 38 - 40 - 57	34	32	38	40	57
Sheet1

Cell Formulas
Range	Formula
C2:G3	C2	=0+MID(SUBSTITUTE(B2," - ",REPT(" ",99)),{1,2,3,4,5}*99-98,99)
Dynamic array formulas.