Excel VBA - Convert numbers that come before a certain substring

Question

I've got a column with cells full of HTML content that includes measurements in centimeters that we need to convert to inches.

The number to convert always precedes "cm" as in <p><strong>Medium - 6.3cm x 7cm</strong></p>

I've been trying to copy the whole cell content into an adjacent cell and identify the numbers to convert by looking between "cm" and a space.

However, there's not always a space between the number and another character as there can be cases like <p>8cm x 5.3cm</p> or blah-10.5cm x 5cm. In some cases it comes at the beginning of the cell in which case there would also be no space, i.e. 12cm x 8cm - blah blah blah.

Can anyone suggest any way to do this by identifying the number to convert as all non-numerical characters preceding the "cm" substring?

What we've got so far tries working around this by replacing characters that show up before the numbers with a space...but finding there are too many possibilities needed for replacement and it also messes up the HTML of course.

I've copied it below for reference in any case. If anyone could help us solve this problem, it'd be immensely appreciated!

Sub ChangeCM2IN()
  Dim X As Long, V As Double, Cell As Range, CM() As String
  Application.ScreenUpdating = False
  For Each Cell In Range("A1", Cells(Rows.Count, "A").End(xlUp))
    CM = Split(Replace(Replace(" " & Cell.Value, "x", "x ", , , vbTextCompare), ">", " "), "cm", , vbTextCompare)
    For X = 0 To UBound(CM) - 1
      V = Format(Mid(CM(X), InStrRev(CM(X), " ")) * 0.393700787401575, "0.0")
      CM(X) = Left(CM(X), InStrRev(CM(X), " ")) & V
    Next
    Cell.Offset(, 1).Value = Join(CM, "in")
  Next
  Application.ScreenUpdating = True
End Sub

Answer 1

Got someone to assist with this and they came up with this really nice solution. Hopefully might be useful to someone else someday!

Sub ChangeCM2IN()
  Dim X As Long, Z As Long, FirstDigit As Long, V As Double, Cell As Range, CM() As String
  Application.ScreenUpdating = False
  For Each Cell In Range("A1", Cells(Rows.Count, "A").End(xlUp))
    CM = Split(" " & Cell.Value, "cm", , vbTextCompare)
    For X = 0 To UBound(CM) - 1
      For Z = Len(CM(X)) To 1 Step -1
        If Mid(CM(X), Z, 1) Like "[!0-9.]" Then
          FirstDigit = Z + 1
          V = Format(Mid(CM(X), FirstDigit) * 0.393700787401575, "0.0")
          Exit For
        End If
      Next
      CM(X) = Left(CM(X), FirstDigit - 1) & V
    Next
    Cell.Offset(, 1).Value = Trim(Join(CM, "in"))
  Next
  Application.ScreenUpdating = True
End Sub