Extract numbers from text into multiple columns

[Giraffe]

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 them perform a calculation from the various numbers eg:

NUMBERS FROM TEXT.jpg

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.

[Giraffe]

Looking at the attachment i am not sure the data is readable so here it is again, hopefully more legible.
NUMBERS FROM TEXT 11pt.jpg

[snb]

Please post a sample workbook.

[Giraffe]

Please find attached sample workbook

[snb]

Sub M_snb()
    Columns(2).SpecialCells(2).TextToColumns Range("C2"), xlFixedWidth, , , , , , , , , Array(Array(0, 9), Array(2, 1), Array(4, 9), Array(7, 1), Array(11, 1), Array(15, 1), Array(16, 9))
End Sub

[Giraffe]

snb, sorry how do i enter this code into the spreadsheet?

[Giraffe]

I have entered the code and run it but it seems to leave an empty cell?

[Rick Rothstein]

Does this macro do what you want...

Sub SplitOutNumbers()
  Dim R As Long, X As Long, LastRow As Long, Text As Variant, Results As Variant
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  For R = 1 To LastRow
    Text = Cells(R, "B").Value
    For X = 1 To Len(Text)
      If Mid(Text, X, 1) Like "[!0-9]" Then Mid(Text, X) = " "
    Next
    Text = Split(Application.Trim(Text), " ", 2)
    Cells(R, "C").Resize(, 4).Value = Array(Text(0), Mid(Text(1), 4), Mid(Text(1), 5, 4), Right(Text(1), 1))
  Next
End Sub

[Giraffe]

Thanks Rick for trying but assuming I have entered it correctly and run it correctly it came out as attached. At the moment I have spent most of this week writing a 50,000 line, 6 column look up table to try to cover every eventuality but this is a somewhat time consuming way and not fool proof, and this only solves one set of data and I have another 2 to solve, so if you can find a way I would be very grateful!

[Rick Rothstein]

[QUOTE=Giraffe;9547]Thanks Rick for trying but assuming I have entered it correctly and run it correctly it came out as attached. At the momen

Thanks Rick for trying but assuming I have entered it correctly and run it correctly it came out as attached.

The reason the code did not work for you is because I designed it for this (which came from the workbook you attached to Message #4)...

	A	B	C	D	E	F
1	100mic Gloss Film 1000mm x 100m on 78mm core	FP22GLO100001003K	22	1000	100	3
2	100mic Gloss Film 1400mm x 1400m x 78mm core	FP22GLO140014003F	22	1400	1400	3
3	125mic Gloss Film 230mm x 60m on 58mm core	FP23GLO023000602	23	230	60	2
4	125mic Gloss Film 230mm x 100m on 58mm core	FP23GLO023001002OUT	23	230	100	2
5	75mic Ultra Matt Film 110mm x 1000m on 78mm core	FR12MAT011010003	12	110	1000	3

but this is from the workbook you attached to Message #9 which you say my code did not work for...

	A	B	C	D	E	F
1	UBG315X500X77	ULTRABOND GLOSS 315mm 500m 77mm core	315	77	77	7
2	UBG315X500X77	ULTRABOND GLOSS 315mm 500m 77mm core	315	77	77	7
3	UBM315X500X77	ULTRABOND MATT 315mm 500m 77mm core	315	77	77	7

Does anything look different about those two layouts to you? Because they sure look different to me!

Now, what does your data layout actually look like... the first or second table above?