Calculate multiple single and double digit numbers from one cell

xdividerx

New Member
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
ABCDEFGHIJKL
1DATEDEFECTSCYCLE # 1CYCLE # 2CYCLE # 3CYCLE # 4CYCLE # 5CYCLE # 6CYCLE # 7CYCLE # 8CYCLE # 9
21/01/20164 15 19 37 40 87 8 3641519374043836-EXAMPLE
32/01/20163 11 20 31 34 42 8 40
43/01/20166 7 9 28 44 45 35 42
54/01/20165 20 23 29 30 44 13 22
65/01/201610 13 16 20 38 40 9 32
76/01/20166 21 22 29 36 42 16 45
87/01/20163 12 36 37 40 41 23 71
98/01/201615 29 31 37 87 44 6 21
109/01/20163 14 18 34 38 44 13 32
1110/01/20162 5 9 10 22 34 1 14 2125910223411421EXAMPLE
1211/01/20167 9 12 17 19 44 6 35
Sheet1

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Fluff

MrExcel MVP, Moderator
+Fluff New.xlsm
ABCDEFGHIJK
1DATEDEFECTSCYCLE # 1CYCLE # 2CYCLE # 3CYCLE # 4CYCLE # 5CYCLE # 6CYCLE # 7CYCLE # 8CYCLE # 9
201/01/20164 15 19 37 40 87 8 3641519374087836
302/01/20163 11 20 31 34 42 8 4031120313442840
403/01/20166 7 9 28 44 45 35 426792844453542
504/01/20165 20 23 29 30 44 13 22520232930441322
605/01/201610 13 16 20 38 40 9 32101316203840932
706/01/20166 21 22 29 36 42 16 45621222936421645
807/01/20163 12 36 37 40 41 23 71312363740412371
908/01/201615 29 31 37 87 44 6 21152931378744621
1009/01/20163 14 18 34 38 44 13 32314183438441332
1110/01/20162 5 9 10 22 34 1 14 2125910223411421
1211/01/20167 9 12 17 19 44 6 357912171944635
Test
Cell Formulas
RangeFormula
C2:K12C2=TRIM(MID(SUBSTITUTE(\$B2," ",REPT(" ",100)),COLUMN(A1)*100-99,100))

gaz_chops

Well-known Member
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

New Member
+Fluff New.xlsm
ABCDEFGHIJK
1DATEDEFECTSCYCLE # 1CYCLE # 2CYCLE # 3CYCLE # 4CYCLE # 5CYCLE # 6CYCLE # 7CYCLE # 8CYCLE # 9
201/01/20164 15 19 37 40 87 8 3641519374087836
302/01/20163 11 20 31 34 42 8 4031120313442840
403/01/20166 7 9 28 44 45 35 426792844453542
504/01/20165 20 23 29 30 44 13 22520232930441322
605/01/201610 13 16 20 38 40 9 32101316203840932
706/01/20166 21 22 29 36 42 16 45621222936421645
807/01/20163 12 36 37 40 41 23 71312363740412371
908/01/201615 29 31 37 87 44 6 21152931378744621
1009/01/20163 14 18 34 38 44 13 32314183438441332
1110/01/20162 5 9 10 22 34 1 14 2125910223411421
1211/01/20167 9 12 17 19 44 6 357912171944635
Test
Cell Formulas
RangeFormula
C2:K12C2=TRIM(MID(SUBSTITUTE(\$B2," ",REPT(" ",100)),COLUMN(A1)*100-99,100))
This worked a treat, thank you!

Fluff

MrExcel MVP, Moderator
Glad we could help & thanks for the feedback.

Replies
7
Views
130
Replies
2
Views
234
Replies
6
Views
109
Replies
15
Views
98
Replies
1
Views
79

1,127,584
Messages
5,625,641
Members
416,124
Latest member
DeMoNloK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back