break concatenate

[johnny52]

In cell a1 I have a concatenate formula with this result 1-2-10-12

I would like to break that out to col b ,col c, col d and col e showing them as separate individual numbers
b1=1 c1=2 d1=10 and e1 =12

at times these numbers will change so I want to accommodate a two digit or one digit scenario like if this 20-3-8-1 occurs.

Is there a left mid right formula to do this

Thanks everbody

 

[jtakw]

Hi,

Use B1 formula if you want the results converted to Real numbers.

Use B9 formula if you may have Leading zeros that you want preserved, result is Text.

Either formula copied across:

Book1
	A	B	C	D	E	F	G
1	1-2-10-12	1	2	10	12
2	20-3-8-1	20	3	8	1
3	11-22-33-44	11	22	33	44
4	5-6-7-8	5	6	7	8
5	999-111-222-333	999	111	222	333
6	1-22-333-4444-55555-666666	1	22	333	4444	55555	666666
7
8
9	1-02-10-12	1	02	10	12
10	20-3-08-1	20	3	08	1
11	11-22-33-044	11	22	33	044
12	05-6-7-8	05	6	7	8
13	999-00111-222-00333	999	00111	222	00333
14	1-22-333-4444-55555-666666	1	22	333	4444	55555	666666
Sheet651

Cell Formulas
Range	Formula
B1	=IFERROR(MID(SUBSTITUTE($A1,"-",REPT(" ",100)),COLUMNS($B1:B1)*100-99,100)+0,"")
B9	=TRIM(MID(SUBSTITUTE($A9,"-",REPT(" ",100)),COLUMNS($B9:B9)*100-99,100))

 

[johnny52]

Wow

Perfect Thanks so much !

 

[jtakw]

You're welcome.