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
  #11  
Old 09-19-2014, 04:20 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,467
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 added a few more conversions, plus some rounding off as orders or magnitude increase.
Code:
Sub EQLConverter()
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)
      .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.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), "(", "")
                  StrConv = Evaluate(Replace(Replace(StrConv, "-", "+"), "'", "*12+0"))
                  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 GPM, do a GPM:CMH conversion
                      If Left(Split(.Text, " ")(j + 1), 3) = "GPM" Then
                        StrOut = StrOut & " " & Format(StrConv / 264.1721 * 60, "0.0") & " CMH" & " (" & StrConv & " GPM)"
                        j = j + 1
                      'If the next 'word' is GAL, do a Gal:Ltr conversion
                      ElseIf 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 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 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 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 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 Left(Split(.Text, " ")(j + 1), 2) = "SQ" And Left(Split(.Text, " ")(j + 2), 2) = "FT" Then
                        StrOut = StrOut & " " & Format(StrConv / 10.76391, "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
          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
End Sub
 Function GetDiameter(sngDia As Single)
Select Case sngDia
  Case Is = 0.125: GetDiameter = 3
  Case Is = 0.1875: GetDiameter = 5
  Case Is = 0.25: GetDiameter = 6
  Case Is = 0.3125: GetDiameter = 8
  Case Is = 0.375: GetDiameter = 10
  Case Is = 0.5: GetDiameter = 13
  Case Is = 0.625: GetDiameter = 16
  Case Is = 0.75: GetDiameter = 19
  Case Is = 0.875: GetDiameter = 22
  Case Is = 1: GetDiameter = 25
  Case Is = 1.25: GetDiameter = 32
  Case Is = 1.5: GetDiameter = 38
  Case Is = 2: GetDiameter = 50
  Case Is = 2.5: GetDiameter = 64
  Case Is = 3: GetDiameter = 75
  Case Is = 4: GetDiameter = 100
  Case Is = 5: GetDiameter = 125
  Case Is = 6: GetDiameter = 150
  Case Is = 8: GetDiameter = 200
  Case Is = 10: GetDiameter = 250
  Case Is = 12: GetDiameter = 300
  Case Is = 14: GetDiameter = 350
  Case Is = 16: GetDiameter = 400
  Case Is = 18: GetDiameter = 450
  Case Is = 20: GetDiameter = 500
  Case Is = 24: GetDiameter = 600
  Case Is = 30: GetDiameter = 750
  Case Else: GetDiameter = Format(sngDia * 25.4, "0")
End Select
End Function
On looking at some of the data you're converting, I'm concerned that some items probably shouldn't be converted. For example, you refer to a 1-1/2" MNPT thread, but those are not interchangeable with a 38mm metric thread. The easiest way to prevent such units being converted is to either remove the spaces (e.g. 1-1/2"MNPT), delete the " (i.e. 1-1/2 MNPT) or insert something else between them (e.g. 1-1/2"-MNPT). I suspect 1.65 SF is a thread size too, but that's not being converted at present because there's no inch indicator there.

Also, you need to ensure the spacing in the initial data are correct. For example, you presently have:
xx, xx-25X ,15 G/HR, 3/8" OZONE OUT, 1/4" COOLING WATER IN/OUT
The 15 G/HR here won't convert because there's no space after the preceding comma.

I've also updated the 'GetDiameter' sub to correct a couple of your conversions, which I didn't do last time.
__________________
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 01:40 PM.

Contact Us - Privacy Statement - Top

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