Extract numbers from text into multiple columns

Giraffe23

New Member
Joined
Sep 22, 2015
Messages
1
I hope i am not repeating someone else's question but i couldn't find the answer to my question -

I have a column of text strings that i need to extract the numbers out of each text string but not as a long number, instead into separate columns in order to perform a calculation from the various numbers. eg:

[TABLE="width: 887"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]100mic Gloss Film 1000mm x 100m on 78mm core
[/TD]
[TD]FP22GLO100001003K
[/TD]
[TD]22
[/TD]
[TD]1000
[/TD]
[TD]100
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]100mic Gloss Film 1400mm x 1400m x 78mm core
[/TD]
[TD]FP22GLO140014003F
[/TD]
[TD]22
[/TD]
[TD]1400
[/TD]
[TD]1400
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]125mic Gloss Film 230mm x 60m on 58mm core
[/TD]
[TD]FP23GLO023000602
[/TD]
[TD]23
[/TD]
[TD]230
[/TD]
[TD]60
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]125mic Gloss Film 230mm x 100m on 58mm core
[/TD]
[TD]FP23GLO023001002OUT
[/TD]
[TD]23
[/TD]
[TD]230
[/TD]
[TD]100
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]75mic Ultra Matt Film 110mm x 1000m on 78mm core
[/TD]
[TD]FR12MAT011010003
[/TD]
[TD]12
[/TD]
[TD]110
[/TD]
[TD]1000
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]75mic Ultra Matt Film 190mm x 500m on 78mm core
[/TD]
[TD]FR12MAT019005003
[/TD]
[TD]12
[/TD]
[TD]190
[/TD]
[TD]500
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]75mic Gloss Film 1400mm x 2000M on 78mm core
[/TD]
[TD]FR12GLO140020003GOLD
[/TD]
[TD]12
[/TD]
[TD]1400
[/TD]
[TD]2000
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]75mic Imagecare Gloss Film 305x75m on 25mm Imagecare
[/TD]
[TD]FR12IMA030500751GLOF
[/TD]
[TD]12
[/TD]
[TD]305
[/TD]
[TD]75
[/TD]
[TD]1
[/TD]
[/TR]
</tbody>[/TABLE]

I have columns A & B and need to extract the numbers from either column A or B into columns C, D, E & F in order to perform a calculation of the amount of film used of the different types. Column C could read the micron thickness from Column A rather than the 2 digit code in Column B as this is just for sorting not calculating.

Thank you in advance for saving me a lot of time - I currently manually extract the numbers each month and there are about 1,000 rows each time.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
<table cellpadding="4px" border="1" cellspacing="1">
<tr><td bgcolor="#DFE3E8"></td><td align="center" width="444" bgcolor="#DFE3E8">A</td><td width="193.2" align="center" bgcolor="#DFE3E8">B</td><td width="138" align="center" bgcolor="#DFE3E8">C</td><td width="144" align="center" bgcolor="#DFE3E8">D</td><td width="129.6" align="center" bgcolor="#DFE3E8">E</td><td align="center" width="129.6" bgcolor="#DFE3E8">F</td></tr>
<tr><td bgcolor="#DFE3E8">1</td><td bgcolor="#FFFFFF">100mic Gloss Film 1000mm x 100m on 78mm core</td><td bgcolor="#FFFFFF">FP22GLO100001003K</td><td bgcolor="#FFFFFF">=MID(B1,3,2)+0</td><td bgcolor="#FFFFFF">=MID(B1,8,4)+0</td><td bgcolor="#FFFFFF">=MID(B1,12,4)+0</td><td bgcolor="#FFFFFF">=MID(B1,16,1)+0</td></tr>
<tr><td bgcolor="#DFE3E8">2</td><td bgcolor="#FFFFFF">100mic Gloss Film 1400mm x 1400m x 78mm core</td><td bgcolor="#FFFFFF">FP22GLO140014003F</td><td bgcolor="#FFFFFF">22</td><td bgcolor="#FFFFFF">1400</td><td bgcolor="#FFFFFF">1400</td><td bgcolor="#FFFFFF">3</td></tr>
<tr><td bgcolor="#DFE3E8">3</td><td bgcolor="#FFFFFF">125mic Gloss Film 230mm x 60m on 58mm core</td><td bgcolor="#FFFFFF">FP23GLO023000602</td><td bgcolor="#FFFFFF">23</td><td bgcolor="#FFFFFF">230</td><td bgcolor="#FFFFFF">60</td><td bgcolor="#FFFFFF">2</td></tr>
<tr><td bgcolor="#DFE3E8">4</td><td bgcolor="#FFFFFF">125mic Gloss Film 230mm x 100m on 58mm core</td><td bgcolor="#FFFFFF">FP23GLO023001002OUT</td><td bgcolor="#FFFFFF">23</td><td bgcolor="#FFFFFF">230</td><td bgcolor="#FFFFFF">100</td><td bgcolor="#FFFFFF">2</td></tr>
<tr><td bgcolor="#DFE3E8">5</td><td bgcolor="#FFFFFF">75mic Ultra Matt Film 110mm x 1000m on 78mm core</td><td bgcolor="#FFFFFF">FR12MAT011010003</td><td bgcolor="#FFFFFF">12</td><td bgcolor="#FFFFFF">110</td><td bgcolor="#FFFFFF">1000</td><td bgcolor="#FFFFFF">3</td></tr>
<tr><td bgcolor="#DFE3E8">6</td><td bgcolor="#FFFFFF">75mic Ultra Matt Film 190mm x 500m on 78mm core</td><td bgcolor="#FFFFFF">FR12MAT019005003</td><td bgcolor="#FFFFFF">12</td><td bgcolor="#FFFFFF">190</td><td bgcolor="#FFFFFF">500</td><td bgcolor="#FFFFFF">3</td></tr>
<tr><td bgcolor="#DFE3E8">7</td><td bgcolor="#FFFFFF">75mic Gloss Film 1400mm x 2000M on 78mm core</td><td bgcolor="#FFFFFF">FR12GLO140020003GOLD</td><td bgcolor="#FFFFFF">12</td><td bgcolor="#FFFFFF">1400</td><td bgcolor="#FFFFFF">2000</td><td bgcolor="#FFFFFF">3</td></tr>
<tr><td bgcolor="#DFE3E8">8</td><td bgcolor="#FFFFFF">75mic Imagecare Gloss Film 305x75m on 25mm Imagecare</td><td bgcolor="#FFFFFF">FR12IMA030500751GLOF</td><td bgcolor="#FFFFFF">12</td><td bgcolor="#FFFFFF">305</td><td bgcolor="#FFFFFF">75</td><td bgcolor="#FFFFFF">1</td></tr>
<tr><td colspan="7"></td></tr>
<tr><td colspan="7" bgcolor="#D7E7F9">Sheet1</td></tr>
</table>
 
Upvote 0

Forum statistics

Threads
1,224,552
Messages
6,179,482
Members
452,915
Latest member
hannnahheileen

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
Back
Top