Calculate multiple single and double digit numbers from one cell

xdividerx

New Member
Joined
Sep 19, 2015
Messages
7
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
 

Some videos you may like

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
Joined
Jun 12, 2014
Messages
55,549
Office Version
  1. 365
Platform
  1. Windows
How about
+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
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS
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

1594640014144.png
 

xdividerx

New Member
Joined
Sep 19, 2015
Messages
7
How about
+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
Joined
Jun 12, 2014
Messages
55,549
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top