Microsoft Office Forums - View Single Post - Search a cell that contains words and numbers and convert the numbers to metric

macropod · 09-17-2014, 06:31 PM

OK, here's an enhanced version. It implements a lookup table for the in:mm conversions. You can add more units to the Function.

Code:

Sub Demo()
Dim lRow As Long, i As Long, j As Long, k As Long, StrTmp As String, StrConv As String, StrOut As String
With ActiveSheet.UsedRange
  'Loop through each cell in column C
  For i = 3 To .Cells.SpecialCells(xlCellTypeLastCell).Row
    With .Cells(i, 3)
      .Select
      StrOut = ""
      'Split the cell results by spaces for parsing
      k = UBound(Split(.Text, " "))
      'Process each 'word'
      For j = 0 To k
        StrTmp = Split(.Text, " ")(j)
        If Len(StrTmp) > 0 Then
          'See what the 'word' ends with
          Select Case Right(StrTmp, 1)
            'If it ends with ', try a ft:m conversion
            Case "'"
              StrConv = Replace(Split(StrTmp, "'")(0), "(", "")
              If IsNumeric(StrConv) Then
                StrOut = StrOut & " " & Format(StrConv * 0.3048, "0.00") & "m" & " (" & StrTmp & ")"
              Else
                StrOut = StrOut & " " & StrTmp
              End If
            'If it ends with ", try a in:mm conversion
            Case """"
              StrConv = Replace(Split(StrTmp, """")(0), "(", "")
              StrConv = Evaluate(Replace(Replace(StrConv, "-", "+"), "'", "*12+0"))
              If IsNumeric(StrConv) Then
                StrOut = StrOut & " " & GetDiameter(CSng(StrConv)) & "mm" & " (" & StrTmp & """)"
              Else
                StrOut = StrOut & " " & StrTmp
              End If
            Case Else
              'If it's not ft/in and we haven't reached the end, look for other possibilities
              If j < k Then
                StrConv = Replace(Split(StrTmp, """")(0), "(", "")
                If IsNumeric(StrConv) Then
                  'If the next 'word' is GPM, do a GPM:CMH conversion
                  If Left(Split(.Text, " ")(j + 1), 3) = "GPM" Then
                    StrOut = StrOut & " " & Format(StrConv / 1000 * 3.78544 * 60, "0.00") & " CMH" & " (" & StrConv & " GPM)"
                    j = j + 1
                  ElseIf Left(Split(.Text, " ")(j + 1), 3) = "GAL" Then
                    StrOut = StrOut & " " & Format(StrConv / 1000 * 3.78544 * 60, "0.00") & " LTR" & " (" & StrConv & " GAL)"
                    j = j + 1
                  ElseIf Replace(Split(.Text, " ")(j + 1), ")", "") = "FT^2" Then
                    StrOut = StrOut & " " & Format(StrConv * 0.02831685, "0.00") & " M^3" & " (" & StrConv & " FT^3)"
                    j = j + 1
                  ElseIf Left(Split(.Text, " ")(j + 1), 2) = "SQ" And Left(Split(.Text, " ")(j + 2), 2) = "FT" Then
                    StrOut = StrOut & " " & Format(StrConv * 0.09290304, "0.00") & " M^2" & " (" & StrConv & " FT^2)"
                    j = j + 2
                  Else
                    StrOut = StrOut & " " & StrTmp
                  End If
                Else
                  StrOut = StrOut & " " & StrTmp
                End If
              Else
                StrOut = StrOut & " " & StrTmp
              End If
          End Select
        Else
          StrOut = StrOut & " "
        End If
      Next
        .Value = StrOut
    End With
  Next
End With
End Sub

Function GetDiameter(sngDia As Single)
Select Case sngDia
  Case Is = 0.25: GetDiameter = 6
  Case Is = 0.5: GetDiameter = 12.5
  Case Is = 0.75: GetDiameter = 20
  Case Is = 1: GetDiameter = 25
  Case Is = 1.25: GetDiameter = 32
  Case Is = 1.5: GetDiameter = 40
  Case Is = 1.75: GetDiameter = 45
  Case Is = 2: GetDiameter = 50
  Case Is = 3: GetDiameter = 75
  Case Is = 4: GetDiameter = 100
  Case Else: GetDiameter = Format(sngDia * 25.4, "0.0")
End Select
End Function

In answer to your question about "And j < K Then", the 'j < k' test is simply checking that we haven't come to the end of the string. That's because the code following it tests for the next element in the string and will generate an error if there isn't one. The same principle is implemented slightly differently in the new code.

If you don't like the precision with which some of the units are output, you can change the "0.00" in the corresponding 'Format' statements.

PS: Your compressor output is expressed as FT^2 but should be FT^3 (or CU. FT.). The code fixes that (by changing FT^2 to FT^3) and also outputs your SQ. FT. units as FT^2, for consistency. Either that or you have a very large filter!