Extract numbers from text into multiple columns

[Rick Rothstein]

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

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