Extract numbers from text into multiple columns

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:

Attachment 1803

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.

Looking at the attachment i am not sure the data is readable so here it is again, hopefully more legible.
Attachment 1804:)

Please post a sample workbook.

Please find attached sample workbook

Code:

---

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

---

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

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

Does this macro do what you want...

Code:

---

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

---

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!

[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

Quote:

---

Originally Posted by Giraffe

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?

It looks like both the first and the second spreadsheet unfortunately. but the second spreadsheet is a full example of the data which should include the data from the first list? The codes (column A) should be consistent but the descriptions (column B) can be changed by the staff, even though they are not supposed to! In spreadsheet 1 I swapped the description and code columns around and forgot i had done so! Sorry!!

Quote:

---

Originally Posted by Giraffe

It looks like both the first and the second spreadsheet unfortunately. but the second spreadsheet is a full example of the data which should include the data from the first list? The codes (column A) should be consistent but the descriptions (column B) can be changed by the staff, even though they are not supposed to! In spreadsheet 1 I swapped the description and code columns around and forgot i had done so! Sorry!!

---

So are you saying the bottom table in Message #10 is your actual layout? If so, where do the numbers in Column F come from? If you say the last digit (which is how it is in the top table), then where did the number in Column E come from?

sorry to clarify, the data in columns A & B from message 10 are the actual data. the other columns are what happened when i ran the code in the file, it filled columns C to F. so only A & B columns are the actual data i have to start with.

Quote:

---

Originally Posted by Giraffe

sorry to clarify, the data in columns A & B from message 10 are the actual data. the other columns are what happened when i ran the code in the file, it filled columns C to F. so only A & B columns are the actual data i have to start with.

---

:confused: Sorry, but that did not clarify anything for me... Message #10 is my message and has two different tables in it with two different problems. I think it best if we hit the reset button and start over. Show me two tables... the first table should show me a sample of your actual data (which I think you are now saying is only two columns wide) and then show me a second table with the results you want to see derived from that same sample data. Once I see them (real data... real results you want from it), I think I will be in a better position to proceed. Whatever you do, do not try to explain or clarify what has been posted earlier... start over like this was your first message and be careful about the accuracy of the existing data you show us and about the accuracy of what you want from that existing data.

OK Sorry!! start again, attached is the file with the columns A & B that I extract from the system and C, D & E are what i would like to see. I have started filling the information in but it takes a long time manually so hopefully there is enough for you to understand what i am trying to achieve. Column C I am using as the identifier (the type of film) so i can then look up the cost for that type of film and multiply it by the width, length to check the cost of each roll of film. The rest of the description is not important. thank you for your efforts.:)

Given the variability of your data, the only thing I will be able to do for the FILM TYPE (Column C) is give you all the text in front of the WIDTH number. About the WIDTH number... with one exception, it looks like all your WIDTH values have an "mm" after them. Again, because of the variability of your data, I will need that "mm" in order to locate the position of the WIDTH number, so I need to know about the exception. On Row 195, you have this...

CODE	DESCRIPTION
250LM	250mc GLS 965 x 25m x 58mm

You are going to have to clarify this item for me and tell me if there are any other items like it. So, is the 965 the WIDTH value and the 25m the LENGTH value? If so, is this the only item in your full inventory of items that are possible to appear in the worksheet that does not have an "mm" after the WIDTH value? I ask because I will have to build an exception routine into the code to identify it, and all other items that are also missing the "mm" on the WIDTH value, so that I can pull out the correct numbers from the DESCRIPTION. On the other hand, if 965 is not the WIDTH value, then are the 25m and 58mm the LENGTH and WIDTH values (reversed from all the other items shown)? Remember, I have no familiarity with your data at all, so you will have to show me a list of all (what I will call) non-standard items for me (that is, items where the WIDTH does not have "mm" after it or where the LENGTH does not have "m" after ite or where the WIDTH comes after the LENGTH).

The problem that I have is that staff are able to modify the descriptions and while they are supposed to follow a system, people mis type etc. so there is no way of knowing all the non-standard items. How about I manually check for missing mm's or m's before running your routine? There are usually only a handful each month but they could be anything. Regarding the line you have picked - that is a good example of a mis typing - it should have read 250mc GLS 965mm(width) x 25m(length) x 58mm(core) - I have added the (xxx) for explanation.

In theory when there is a full code in the code column the description should be accurate as they should not have altered the description, but when there is a short or no code it is because the system did not have a code for that size and type of film so they have manually entered the description. We have 100 types of film, in theory an infinite number of lengths and and infinite number of widths as the customer specifies what width and length they would like a particular type of film - in reality there are probably about 30 common widths asked for and about 50 common lengths but then there are always the odd ones!

Quote:

---

Originally Posted by Giraffe

How about I manually check for missing mm's or m's before running your routine?

---

No, the idea behind using code is to eliminate as much work for the user as possible. I think I have a working solution for you... the code will process all lines that have a #mm #m or #mm x #m (where # is a number) shaped text in them, otherwise the description will be highlighted in red. In running the sample you posted, I found problems on Rows 195 (no "mm"), 249 (no "mm"), 257 thru 264 (no space after the LENGTH's "m" unit), 265 (no "m"), 269 (no "m"), 323 thru 337 (no space after the LENGTH's "m" unit) and 342 (no "m")... those you will have to fill in manually (and perhaps correct the description where needed). Here is my code...

Code:

---

Sub FilmTypeWidthLength()   Dim R As Long, X As Long, MM As Long, SP As Long   Dim Txt As Variant, Data As Variant, Results As Variant   Data = Range("B2", Cells(Rows.Count, "B").End(xlUp))   Columns("B").Interior.ColorIndex = xlColorIndexNone   ReDim Results(1 To UBound(Data), 1 To 3)   For R = 1 To UBound(Data)     MM = InStr(1, Replace(Data(R, 1), "x", " ", , , vbTextCompare), "mm ", vbTextCompare)     If MM = 0 Then       Cells(R + 1, "B").Interior.ColorIndex = 3     Else       SP = InStrRev(Data(R, 1), " ", MM)       Txt = Split(Application.Trim(Mid(Replace(Data(R, 1), "x", " ", , , vbTextCompare), SP + 1)))       If Len(Txt(0)) < 3 Or Len(Txt(0)) < 2 Then         Cells(R + 1, "B").Interior.ColorIndex = 3       ElseIf (Not Right(Txt(0), 3) Like "#[Mm][Mm]") Or (Not Right(Txt(1), 2) Like "#[Mm]") Then         Cells(R + 1, "B").Interior.ColorIndex = 3       Else         Results(R, 1) = Left(Data(R, 1), SP - 1)         Results(R, 2) = Mid(Data(R, 1), SP + 1, MM - SP - 1)         Results(R, 3) = Val(Mid(Replace(Data(R, 1), "x", " ", , , vbTextCompare), MM + 3))       End If     End If   Next   Range("C2:E" & UBound(Results)) = Results End Sub

---