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

Reply
 
Thread Tools Display Modes
  #1  
Old 09-23-2014, 10:49 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
Quote:
Originally Posted by Carchee View Post
The conversions we use for pipe diameters are NPS and DN standards, specifically the numbers I had within the 'GetDiameter' sub. The following link will explain a bit further what I'm talking about if it interests you.
http://www.engineeringtoolbox.com/np...izes-d_45.html
Some of those conversions for sizes less than 1in look decidedly dodgy to me. Equating 1/8in with 6mm whilst also equating 3/8 with 10mm defies logic (3*6mm = 18mm, not 10mm). And I've certainly bought 5/16in ID hose with both that and the metric equivalent (8mm) printed on it.

Try the following update. Note that I've added yet more conversions.

You'll need to tidy up more of your data, too. For example, you should remove any existing metric figures - you wouldn't want a double conversion for:
TRERICE AX9, 7 INCH (180mm)ADJUSTABLE ANGLE W/ THERMOWELL
In this case, I'd suggest deleting the (180mm). You might also want to add a 7in:180mm conversion to your diameters. The output would then become:
TRERICE AX9, 180 mm (7") ADJUSTABLE ANGLE W/ THERMOWELL
which is consistent with how the other conversions are done. Similarly, you'll need to clean up data like:
WIKA (TYPE PER INSTALLATION), LIQUID FILLED 2.5 INCH (63mm) DIA. 0-30 Hg/0-30 PSI RANGE
changing this to, say:
WIKA (TYPE PER INSTALLATION), LIQUID FILLED 2.5 INCH DIA. 0-30 Hg / 0-30 PSI RANGE
which would then be turned into:


WIKA (TYPE PER INSTALLATION), LIQUID FILLED 64 mm (2.5") DIA. 0-762mm (0-30") Hg 0-207 KPA (0-30 PSI) RANGE
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 PSI, do a PSI:KPA conversion
                      If Left(Split(.Text, " ")(j + 1), 3) = "PSI" Then
                        StrOut = StrOut & " " & Round(StrConv * 6.894757, 0) & " KPA" & " (" & StrConv & " PSI)"
                      'If the next 'word' is IN/INCH, do an in:mm conversion
                      ElseIf 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 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 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
                    ' 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 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 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
End Sub
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

« 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 10:18 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