Extract numbers from text into multiple columns

**Giraffe** · 10-05-2015, 01:39 PM

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!!

**Rick Rothstein** · 10-05-2015, 02:18 PM

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?

**Giraffe** · 10-05-2015, 02:55 PM

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.

**Rick Rothstein** · 10-05-2015, 07:59 PM

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.

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.

**Giraffe** · 10-06-2015, 07:07 PM

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.

**Rick Rothstein** · 10-06-2015, 08:16 PM

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).

**Giraffe** · 10-06-2015, 08:46 PM

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!

**Rick Rothstein** · 10-06-2015, 10:41 PM

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