Microsoft Office Forums

Go Back   Microsoft Office Forums > >
Reload this Page Search a cell that contains words and numbers and convert the numbers to metric
Register FAQ

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #16  
Old 09-24-2014, 06:51 PM
macropod's Avatar
macropod macropod is offline Search a cell that contains words and numbers and convert the numbers to metric Windows 7 64bit Search a cell that contains words and numbers and convert the numbers to metric Office 2010 32bit
Administrator
  
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,536
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default
Try the following update. Note that I've retained the pressure conversions in the code, but they're commented-out so they won't do anything. That means you can reinstate them later on if need be.


Code:
Sub EQLConverter()
Application.ScreenUpdating = False
Dim lRow As Long, i As Long, j As Long, k As Long
Dim StrTmp As String, StrBit 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)
      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 UCase(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.0") & "m" & " (" & StrTmp & ")"
              Else
                StrOut = StrOut & " " & StrTmp
              End If
            'If it ends with ", try an 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
              Select Case Right(StrTmp, 2)
                'If it ends with FT, try an ft:m conversion
                Case "FT"
                  StrConv = Replace(Split(StrTmp, "FT")(0), "(", "")
                  If IsNumeric(StrConv) Then
                    StrOut = StrOut & " " & Format(StrConv * 0.3048, "0.0") & "m" & " (" & StrConv & "')"
                  Else
                    StrOut = StrOut & " " & StrTmp
                  End If
                'If it ends with IN, try an in:mm conversion
                Case "IN"
                  StrConv = Replace(Split(StrTmp, "IN")(0), "(", "")
                  If IsNumeric(Left(StrConv, 1)) Then
                    StrConv = Evaluate(Replace(Replace(StrConv, "-", "+"), "'", "*12+0"))
                  End If
                  If IsNumeric(StrConv) Then
                    StrOut = StrOut & " " & GetDiameter(CSng(StrConv)) & "mm" & " (" & StrConv & """)"
                  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 IN/INCH, do an in:mm conversion
                      If UCase(Left(Split(.Text, " ")(j + 1), 2)) = "IN" Then
                        StrConv = Evaluate(Replace(Replace(StrConv, "-", "+"), "'", "*12+0"))
                        StrOut = StrOut & " " & GetDiameter(CSng(StrConv)) & " mm" & " (" & StrConv & """)"
                        j = j + 1
                      'If the next 'word' is GPM, do a GPM:CMH conversion
                      ElseIf UCase(Left(Split(.Text, " ")(j + 1), 3)) = "GPM" Then
                        StrOut = StrOut & " " & Format(StrConv * 3.785412, "0.0") & " L/Min" & " (" & StrConv & " GPM)"
                        j = j + 1
                      'If the next 'word' is GAL, do a Gal:Ltr conversion
                      ElseIf UCase(Left(Split(.Text, " ")(j + 1), 3)) = "GAL" Then
                        Select Case StrConv * 3.785412
                          Case Is >= 100
                            StrOut = StrOut & " " & Format(StrConv * 0.3785412, "0") * 10 & " Ltr" & " (" & StrConv & " GAL)"
                          Case Is >= 10, Is < 100
                            StrOut = StrOut & " " & Format(StrConv * 3.785412, "0") & " Ltr" & " (" & StrConv & " GAL)"
                          Case Else
                            StrOut = StrOut & " " & Format(StrConv * 3.785412, "0.0") & " Ltr" & " (" & StrConv & " GAL)"
                        End Select
                        j = j + 1
                      'If the next 'word' is GPD, do a GPD:LPD conversion
                      ElseIf UCase(Left(Split(.Text, " ")(j + 1), 3)) = "GPD" Then
                        Select Case StrConv * 3.785412
                          Case Is >= 100
                            StrOut = StrOut & " " & Format(StrConv * 0.3785412, "0") * 10 & " LPD" & " (" & StrConv & " GPD)"
                          Case Is >= 10, Is < 100
                            StrOut = StrOut & " " & Format(StrConv * 3.785412, "0") & " LPD" & " (" & StrConv & " GPD)"
                          Case Else
                            StrOut = StrOut & " " & Format(StrConv * 3.785412, "0.0") & " LPD" & " (" & StrConv & " GPD)"
                        End Select
                        j = j + 1
                      'If the next 'word' is G/HR, do a G/HR:L/HR conversion
                      ElseIf UCase(Left(Split(.Text, " ")(j + 1), 4)) = "G/HR" Then
                        Select Case StrConv * 3.785412
                          Case Is >= 100
                            StrOut = StrOut & " " & Format(StrConv * 0.3785412, "0") * 10 & " LPH" & " (" & StrConv & " GPH)"
                          Case Is >= 10, Is < 100
                            StrOut = StrOut & " " & Format(StrConv * 3.785412, "0") & " LPH" & " (" & StrConv & " GPH)"
                          Case Else
                            StrOut = StrOut & " " & Format(StrConv * 3.785412, "0.0") & " LPH" & " (" & StrConv & " GPH)"
                        End Select
                        j = j + 1
                      'If the next 'word' is LB, do a LB:KG conversion
                      ElseIf UCase(Left(Split(.Text, " ")(j + 1), 2)) = "LB" Then
                        Select Case StrConv / 2.20462
                          Case Is >= 100
                            StrOut = StrOut & " " & Format(StrConv / 2.20462, "0") * 10 & " KG" & " (" & StrConv & " LB)"
                          Case Is >= 10, Is < 100
                            StrOut = StrOut & " " & Format(StrConv / 2.20462, "0") & " KG" & " (" & StrConv & " LB)"
                          Case Else
                            StrOut = StrOut & " " & Format(StrConv / 2.20462, "0.0") & " KG" & " (" & StrConv & " LB)"
                        End Select
                        j = j + 1
                      'If the next 'word' is FT^2, do a FT^2:M^2 conversion
                      ElseIf UCase(Replace(Split(.Text, " ")(j + 1), ")", "")) = "FT^2" Then
                        StrOut = StrOut & " " & Format(StrConv / 10.76391, "0.00") & " M^2" & " (" & StrConv & " FT^2)"
                        j = j + 1
                      'If the next two 'words' are SQ. FT., do a FT^2:M^2 conversion
                      ElseIf UCase(Left(Split(.Text, " ")(j + 1), 2)) = "SQ" And UCase(Left(Split(.Text, " ")(j + 2), 2)) = "FT" Then
                        StrOut = StrOut & " " & Format(StrConv / 10.76391, "0.00") & " M^2" & " (" & StrConv & " FT^2)"
                        j = j + 2
                      'If the next 'word' is PSI, do a PSI:KPA conversion
                      ElseIf UCase(Left(Split(.Text, " ")(j + 1), 3)) = "PSI" Then
                        StrOut = StrOut & " " & Round(StrConv * 6.894757, 0) & " KPA" & " (" & StrConv & " PSI)"
                      Else
                        StrOut = StrOut & " " & StrTmp
                      End If
                    ' Test for a cell containing a numeric range
                    'ElseIf InStr(StrConv, "-") > 0 Then
                      'If IsNumeric(Split(StrConv, "-")(0)) And IsNumeric(Split(StrConv, "-")(1)) Then
                        'If the next 'word' is PSI, do a PSI:KPA conversion
                        'If Left(Split(.Text, " ")(j + 1), 3) = "PSI" Then
                          'StrOut = StrOut & " " & Round(Split(StrConv, "-")(0) * 6.894757, 0) & "-" & Round(Split(StrConv, "-")(1) * 6.894757) & " KPA" & " (" & StrConv & " PSI)"
                          'j = j + 1
                        'End If
                        'If the next 'word' begins with IN or Hg, do an in:mm conversion
                        'If UCase(Left(Split(.Text, " ")(j + 1), 2)) = "HG" Then
                          'StrOut = StrOut & " " & Round(Split(StrConv, "-")(0) * 25.4, 0) & "-" & Round(Split(StrConv, "-")(1) * 25.4, 0) & "mm" & " (" & StrConv & """) Hg"
                          'j = j + 2
                        'End If
                        'If UCase(Left(Split(.Text, " ")(j + 1), 2)) = "IN" Then
                          'StrOut = StrOut & " " & Round(Split(StrConv, "-")(0) * 25.4, 0) & "-" & Round(Split(StrConv, "-")(1) * 25.4, 0) & "mm" & " (" & StrConv & """)"
                          'j = j + 2
                        'End If
                      'End If
                    Else
                      StrOut = StrOut & " " & StrTmp
                    End If
                  Else
                    StrOut = StrOut & " " & StrTmp
                  End If
              End Select
          End Select
        Else
          StrOut = StrOut & " "
        End If
      Next
      StrOut = Trim(StrOut)
      If InStr(StrOut, " X ") > 0 Then
        StrConv = ""
        For j = 0 To UBound(Split(StrOut, " X ")) Step 2
          StrTmp = Split(StrOut, " X ")(j)
          For k = 0 To UBound(Split(StrTmp, " ")) - 1
            StrConv = StrConv & " " & Split(StrTmp, " ")(k)
          Next
          StrConv = StrConv & " * " & Split(Split(StrOut, " X ")(j + 1), " ")(0)
          StrConv = StrConv & " " & Replace(Split(StrTmp, " ")(k), ")", " x ")
          StrConv = StrConv & Replace(Split(Split(StrOut, " X ")(j + 1), " ")(1), "(", "")
        Next
        For j = 1 To UBound(Split(StrOut, " X ")) Step 2
          StrTmp = Split(StrOut, " X ")(j)
          For k = 2 To UBound(Split(StrTmp, " "))
            StrConv = StrConv & " " & Split(StrTmp, " ")(k)
          Next
        Next
      StrOut = StrConv
      End If
      .Value = Trim(StrOut)
    End With
  Next
End With
Application.ScreenUpdating = True
End Sub
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
 

« Previous Thread | Next Thread »


Similar Threads
Thread Thread Starter Forum Replies Last Post
Words in my document were converted to numbers???? MikeD23 Word 1 08-26-2012 11:09 AM
can word convert numbers? koolfire Word 2 01-07-2012 02:12 PM
Search a cell that contains words and numbers and convert the numbers to metric Convert numbers to a specific text string francis Excel 1 10-06-2011 01:43 PM
Numbers in Words janak Excel 2 12-19-2010 08:53 PM
Numbers Convert in word in MS Access towhid Office 0 08-19-2010 01:17 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 08:19 PM.

Contact Us - Privacy Statement - Top

Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2026, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2026 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft