Calculate multiple single and double digit numbers from one cell

[xdividerx]

Hi All,

I have searched online and in these forums but was unable to find a way (using formulas) to extract multiple single and double digits from on cell.

Please see the below examples. My data source alternates between 8 and 9 numbers in column B.

Am requesting assistance with formulas to display split number into columns C through to K.

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	DATE	DEFECTS	CYCLE # 1	CYCLE # 2	CYCLE # 3	CYCLE # 4	CYCLE # 5	CYCLE # 6	CYCLE # 7	CYCLE # 8	CYCLE # 9
2	1/01/2016	4 15 19 37 40 87 8 36	4	15	19	37	40	43	8	36	-	EXAMPLE
3	2/01/2016	3 11 20 31 34 42 8 40
4	3/01/2016	6 7 9 28 44 45 35 42
5	4/01/2016	5 20 23 29 30 44 13 22
6	5/01/2016	10 13 16 20 38 40 9 32
7	6/01/2016	6 21 22 29 36 42 16 45
8	7/01/2016	3 12 36 37 40 41 23 71
9	8/01/2016	15 29 31 37 87 44 6 21
10	9/01/2016	3 14 18 34 38 44 13 32
11	10/01/2016	2 5 9 10 22 34 1 14 21	2	5	9	10	22	34	1	14	21	EXAMPLE
12	11/01/2016	7 9 12 17 19 44 6 35
Sheet1

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	DATE	DEFECTS	CYCLE # 1	CYCLE # 2	CYCLE # 3	CYCLE # 4	CYCLE # 5	CYCLE # 6	CYCLE # 7	CYCLE # 8	CYCLE # 9
2	01/01/2016	4 15 19 37 40 87 8 36	4	15	19	37	40	87	8	36
3	02/01/2016	3 11 20 31 34 42 8 40	3	11	20	31	34	42	8	40
4	03/01/2016	6 7 9 28 44 45 35 42	6	7	9	28	44	45	35	42
5	04/01/2016	5 20 23 29 30 44 13 22	5	20	23	29	30	44	13	22
6	05/01/2016	10 13 16 20 38 40 9 32	10	13	16	20	38	40	9	32
7	06/01/2016	6 21 22 29 36 42 16 45	6	21	22	29	36	42	16	45
8	07/01/2016	3 12 36 37 40 41 23 71	3	12	36	37	40	41	23	71
9	08/01/2016	15 29 31 37 87 44 6 21	15	29	31	37	87	44	6	21
10	09/01/2016	3 14 18 34 38 44 13 32	3	14	18	34	38	44	13	32
11	10/01/2016	2 5 9 10 22 34 1 14 21	2	5	9	10	22	34	1	14	21
12	11/01/2016	7 9 12 17 19 44 6 35	7	9	12	17	19	44	6	35
Test

Cell Formulas
Range	Formula
C2:K12	C2	=TRIM(MID(SUBSTITUTE($B2," ",REPT(" ",100)),COLUMN(A1)*100-99,100))

 

[gaz_chops]

Probably a better solution, but this appears to work

In C2 enter
=LEFT(B2,2)+0

In D2 enter
=IF(COLUMN(B1)>(LEN($B2)-LEN(SUBSTITUTE($B2," ",""))+1),"",RIGHT(TRIM(REPLACE(SUBSTITUTE($B2," ",REPT(" ",999),COLUMN(B1)),99,999,"")),2)+0

 

[xdividerx]

How about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	DATE	DEFECTS	CYCLE # 1	CYCLE # 2	CYCLE # 3	CYCLE # 4	CYCLE # 5	CYCLE # 6	CYCLE # 7	CYCLE # 8	CYCLE # 9
2	01/01/2016	4 15 19 37 40 87 8 36	4	15	19	37	40	87	8	36
3	02/01/2016	3 11 20 31 34 42 8 40	3	11	20	31	34	42	8	40
4	03/01/2016	6 7 9 28 44 45 35 42	6	7	9	28	44	45	35	42
5	04/01/2016	5 20 23 29 30 44 13 22	5	20	23	29	30	44	13	22
6	05/01/2016	10 13 16 20 38 40 9 32	10	13	16	20	38	40	9	32
7	06/01/2016	6 21 22 29 36 42 16 45	6	21	22	29	36	42	16	45
8	07/01/2016	3 12 36 37 40 41 23 71	3	12	36	37	40	41	23	71
9	08/01/2016	15 29 31 37 87 44 6 21	15	29	31	37	87	44	6	21
10	09/01/2016	3 14 18 34 38 44 13 32	3	14	18	34	38	44	13	32
11	10/01/2016	2 5 9 10 22 34 1 14 21	2	5	9	10	22	34	1	14	21
12	11/01/2016	7 9 12 17 19 44 6 35	7	9	12	17	19	44	6	35
Test

Cell Formulas
Range	Formula
C2:K12	C2	=TRIM(MID(SUBSTITUTE($B2," ",REPT(" ",100)),COLUMN(A1)*100-99,100))

This worked a treat, thank you!

 

[Fluff]

Glad we could help & thanks for the feedback.