Search a cell that contains words and numbers and convert the numbers to metric

Carchee · #1 09-11-2014, 10:44 AM

I have an equipment list that displays pumps, filters and other equipment for my work. The list gives info like manufacture, model number, flows, head, areas and pipe sizes. The list is given in empirical units (feet, ft^2, inches, gallons, etc.) We create several of these lists everyday and a good majority of them need to be in metric units (meters, m^2, mm, cubic meters, cmh, etc.).

Our way of converting them now is to do it manually, double clicking the cell and entering the conversion. I would like to create code to do this for me, including the converting. I would like the code to look for the GPM or TDH or " (inch) and convert the number just before the units.

I have attached a sample of what the list looks like, C3 shows the original content with empirical units and C4 shows what we would like after the conversion to metric.

Before I write code I would like to know if it's even possible. I'm not sure if it's possible because the flows can range from 2 digits to 5 digits and I'm having a hard time coming up with a way to search a cell for a number that have a different amount of digits. Other number data might be different as well.

Thanks for any help.

excelledsoftware · #2 09-11-2014, 10:39 PM

I dont know a whole lot about empirical and metric conversion but I do know that VBA can do what you are looking for. Are you using a particular formula to convert them? VBA has the ability to check a string for the amount characters easily. It can also evaluate each character in the string the possibilities are close to unlimited. If you can tell me the formula you use or what exact conditions you need this can be written.
Thanks

Carchee · #3 09-15-2014, 08:36 AM

Thank you Paul, I'll see what I can do with this code. It should be enough to figure some stuff out.

excelledsoftware: There are certain formulas that I'll be using to convert but I can just insert them into code easily.

The hard part is that the cell contains more that one set of numbers and I would like to convert both sets. If you look at the attached excel sheet from my first post you can see that I have 233 GPM @ 65'. So I would be converting GPM (gallon per minute) to CMH (cubic meters per hour) and convert the 65 feet to meters. Also The GPM will fluctuate quite often and have different amount of digits if that makes sense.
The right side of the cell will never change, the only thing that will change are the numbers, so my thought was to use something like this

Code:

=(RIGHT(A3,SEARCH("GPM",A3)-1))

or like this

Code:

=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1)))

But I can't get the previous code to work either.

I'll also be converting from inches to mm, in the text a " symbol is used for inches, so I would search for " and convert the number to mm, sometimes there are two " lengths in the same cell that I would convert

I would also like to add parenthesis to the original text (look at my attached sheet for clarification

This is a lot to do and I'm just starting out so it might take me a little bit. I am happy to hear that it is possible. Thanks for your help. Let me know if I can clear things up.

macropod · #4 09-11-2014, 11:07 PM

Here's some code to get you started:

Code:

Sub Demo()
Dim lRow As Long, i As Long, j As Long, k As Long, StrTmp 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)
        'See what the 'word' ends with
        Select Case Right(StrTmp, 1)
          'If it ends with ', try a ft:m conversion
          Case "'"
            StrTmp = Split(StrTmp, "'")(0)
            If IsNumeric(StrTmp) Then
              StrOut = StrOut & " " & Format(StrTmp * 0.3048, "0.00") & "m" & " (" & StrTmp & "')"
            Else
              StrOut = StrOut & " " & StrTmp
            End If
          'If it ends with ", try a in:cm conversion
          Case """"
            StrTmp = Split(StrTmp, """")(0)
            If IsNumeric(StrTmp) Then
              StrOut = StrOut & " " & Format(StrTmp * 2.54, "0.00") & "cm" & " (" & StrTmp & """)"
            'If it ends with " and contains ', try a ft&in:cm conversion
            ElseIf InStr(StrTmp, "'") > 0 Then
              If IsNumeric(Split(StrTmp, "'")(0)) And IsNumeric(Split(StrTmp, "'")(1)) Then
                StrOut = StrOut & " " & Format(Split(StrTmp, "'")(0) * 0.3048 + Split(StrTmp, "'")(1) * 2.54, "0.00") & "cm" & " (" & StrTmp & """)"
              Else
                StrOut = StrOut & " " & StrTmp
              End If
            Else
              StrOut = StrOut & " " & StrTmp
            End If
          Case Else
            'If it's not ft/in, look for other possibilities
            If IsNumeric(StrTmp) And j < k Then
              'If the next 'word' is GPM, do a GPM:CMH conversion
              If Split(.Text, " ")(j + 1) = "GPM" Then
                StrOut = StrOut & " " & Format(StrTmp / 1000 * 3.78544 * 60, "0.00") & " CMH" & " (" & StrTmp & " GPM)"
                j = j + 1
              Else
                StrOut = StrOut & " " & StrTmp
              End If
            Else
              StrOut = StrOut & " " & StrTmp
            End If
        End Select
      Next
        .Value = StrOut
    End With
  Next
End With
End Sub

Carchee · #5 09-15-2014, 08:52 AM

Okay so I just went through your code Paul and so far it's really straight forward. I plugged it into my sheets and it works perfectly, now I just need to tie it into all my other conversions. I'll keep you posted.

Carchee · #6 09-17-2014, 02:05 PM

Okay Paul,

I've gone through and tried to add other conversions and all have failed. The attached sample is all conversion that I need. I thought it would be easy to add the others in but for some reason I can't get them to work, obviously it's my lack of coding knowledge.

All inch (") units need to be converted to a nominal pipe diameter which means a simple formula is close but isn't going to work. You'll see on the attachment also that a lot of those inch dimensions contain dashes (-) and a forward slash (/). The pipe sizes never change so the code could recognize the pipe size (essentially text) and then convert it to the nominal pipe size. The attachment shows some example of that. I tried finding ways to do this but couldn't find anything.

One question about your code: What does

Code:

And j < k Then

do exactly. The way I understand your code is first it searches the right most part of the cell for numbers and converts those numbers then it moves onto the second right most number part within the cell and it look like the "And j < K Then" is telling it to look at the second part. Am I far off?

Here is my attempt to add my own code.....be nice

Code:

Sub EQLConverter()
Dim lRow As Long, i As Long, j As Long, k As Long, StrTmp 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)
        'See what the 'word' ends with
        Select Case Right(StrTmp, 1)
          'If it ends with ', try a ft:m conversion
          Case "'"
            StrTmp = Split(StrTmp, "'")(0)
            If IsNumeric(StrTmp) Then
              StrOut = StrOut & " " & Format(StrTmp * 0.3048, "0.0") & "m" & " (" & StrTmp & "')"
            Else
              StrOut = StrOut & " " & StrTmp
            End If
            
         '*******INCH TO MM CONVERSION (NOMINAL DIAMETERS, NO FORMULAS)*******
            
          'If it ends with ", try a in:mm conversion
         ' Case """"
           ' StrTmp = Split(StrTmp, """")(0)
           ' If IsNumeric(StrTmp) Then
            '  StrOut = StrOut & " " & Format(StrTmp * 25.4, "0.0") & "mm" & " (" & StrTmp & """)"
           ' Else
             ' StrOut = StrOut & " " & StrTmp
           ' End If
           
           '*******TRYING AREA CONVERSIONS****
           
           
         'If it ends with 2, try a FT^2:M^2 conversion
            ' Case "2"
           ' StrTmp = Split(StrTmp, "2")(0)
           ' If IsNumeric(StrTmp) Then
             ' StrOut = StrOut & " " & Format(StrTmp * 0.0929, "0.0") & "M^2" & " (" & StrTmp & "FT^2)"
           ' Else
              'StrOut = StrOut & " " & StrTmp
           ' End If
           'If it ends with ., try a FT^2:M^2 conversion
          'Case "."
            'StrTmp = Split(StrTmp, ".")(0)
            'If IsNumeric(StrTmp) Then
              'StrOut = StrOut & " " & Format(StrTmp * 0.929, "0.0") & "M^2" & " (" & StrTmp & "SQ. FT.)"
            'Else
              'StrOut = StrOut & " " & StrTmp
            'End If
         'If it ends with ), try a FT^2:M^2 conversion
         ' Case ")"
            'StrTmp = Split(StrTmp, ")(0)
            'If IsNumeric(StrTmp) Then
             ' StrOut = StrOut & " " & Format(StrTmp * 0.929, "0.0") & "M^2" & " (" & StrTmp & "FT^2)"
           ' Else
              'StrOut = StrOut & " " & StrTmp
           ' End If
           
            '***************
                 
            
          Case Else
            'If it's not ft/in, look for other possibilities
            If IsNumeric(StrTmp) And j < k Then
              'If the next 'word' is GPM, do a GPM:CMH conversion
              If Split(.Text, " ")(j + 1) = "GPM" Then
                StrOut = StrOut & " " & Format(StrTmp / 1000 * 3.78544 * 60, "0.0") & " CMH" & " (" & StrTmp & " GPM)"
                j = j + 1
              'If it ends with GAL, try a GAL:M^3 conversion
            ElseIf Split(.Text, " ")(j + 1) = "GAL" Then
                StrOut = StrOut & " " & Format(StrTmp * 0.003785, "0.0") & " M^3" & " (" & StrTmp & " GAL)"
                j = j + 1
             'If it ends with FT^2, try a FT^2:M^2 conversion
            ElseIf Split(.Text, " ")(j + 1) = "FT^2)" Then
                StrOut = StrOut & " " & Format(StrTmp * 0.0929, "0.0") & " M^2" & " (" & StrTmp & " FT^2)"
                j = j + 1
               Else
              StrOut = StrOut & " " & StrTmp
            End If
             Else
              StrOut = StrOut & " " & StrTmp
            End If
            
        End Select
      Next
        .Value = StrOut
    End With
  Next
End With
End Sub

macropod · #7 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!

Carchee · #8 09-18-2014, 09:02 AM

Paul,

I figured I would need to use some sort of look up table for the in:mm conversion, I just didn't know how to implement it.

Of course I forgot one more piece of equipment, a basket strainer, you can see it added to the list in the attachment. I double checked all my equipment and made sure we didn't have any more. I tried implementing it into the code and couldn't get it to work. What I have shown I know is wrong and doesn't even make sense to me, I was just trying everything. I really am trying to understand this stuff, I think my problem is that I only understand parts of the code and not all of it. So it's hard for me to add new code beyond my knowledge, which is very limited.

I get a "Run-Time error '9': Subscript out of range", it only pops up when the filter type with SQ. FT. units aren't in the list. I tried playing with it and couldn't get it to work. All the code works perfectly, converts and does exactly what I want it to with the exception of that one error.

Also, you mentioned the compressor and its units. Although you are correct a compressor would have a volume unit the actual unit given is a filter surface area. The note states that a compressor is included. The note is a little misleading if you aren't used to working with it. I changed the code to account for the area. Good eye!

My current code:

Code:

Sub EQLConverter()
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.0") & "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
            'If it ends with IN, try a 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" & " (" & StrTmp & " X " & 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.0") & " CMH" & " (" & StrConv & " GPM)"
                    j = j + 1
                  ElseIf Left(Split(.Text, " ")(j + 1), 3) = "GAL" Then
                    StrOut = StrOut & " " & Format(StrConv / 1000 * 3.78544, "0.0") & " M^3" & " (" & StrConv & " GAL)"
                    j = j + 1
                  ElseIf Replace(Split(.Text, " ")(j + 1), ")", "") = "FT^2" Then
                    StrOut = StrOut & " " & Format(StrConv / 1000 * 9.290304, "0.00") & " M^2" & " (" & StrConv & " FT^2)"
                    j = j + 1
                  ElseIf Left(Split(.Text, " ")(j + 1), 2) = "SQ" And Left(Split(.Text, " ")(j + 2), 2) = "FT" Then
                    StrOut = StrOut & " " & Format(StrConv / 1000 * 9.290304, "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.125: GetDiameter = 6
  Case Is = 0.1875: GetDiameter = 7
  Case Is = 0.25: GetDiameter = 8
  Case Is = 0.375: GetDiameter = 10
  Case Is = 0.5: GetDiameter = 15
  Case Is = 0.625: GetDiameter = 18
  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 = 2: GetDiameter = 50
  Case Is = 2.5: GetDiameter = 65
  Case Is = 3: GetDiameter = 80
  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.0")
End Select
End Function

Thank you Paul.

macropod · #9 09-18-2014, 05:33 PM

Try the following update. Incorporating strainer-type dimensions required a fair bit more code.

I don't understand your reference to;

Quote:

a "Run-Time error '9': Subscript out of range", it only pops up when the filter type with SQ. FT. units aren't in the list

as there's no list for these anyway and I'm not getting an error with the code I posted.

Your 'IN' conversion code wouldn't have worked because it was embedded inside the part of the Select Case statement that only looked at the right-most character, whereas you need to test the two right-most characters.

Some of your conversion factor changes were wrong - I've corrected those too.

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 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
            'If it ends with IN, try a in:mm conversion
            Case "IN"
            Case Else
              Select Case Right(StrTmp, 2)
                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
                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 * 0.003785412 * 60, "0.0") & " CMH" & " (" & StrConv & " GPM)"
                        j = j + 1
                      ElseIf Left(Split(.Text, " ")(j + 1), 3) = "GAL" Then
                        StrOut = StrOut & " " & Format(StrConv * 0.003785412, "0.00") & " M^3" & " (" & StrConv & " GAL)"
                        j = j + 1
                      ElseIf Replace(Split(.Text, " ")(j + 1), ")", "") = "FT^2" Then
                        StrOut = StrOut & " " & Format(StrConv * 0.09290304, "0.00") & " M^2" & " (" & StrConv & " FT^2)"
                        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
          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 = 6
  Case Is = 0.1875: GetDiameter = 7
  Case Is = 0.25: GetDiameter = 8
  Case Is = 0.375: GetDiameter = 10
  Case Is = 0.5: GetDiameter = 15
  Case Is = 0.625: GetDiameter = 18
  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 = 2: GetDiameter = 50
  Case Is = 2.5: GetDiameter = 65
  Case Is = 3: GetDiameter = 80
  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

FWIW, You can download a free unit converter (Convert) from: http://joshmadison.com/convert-for-windows/

Carchee · #10 09-19-2014, 08:10 AM

The code works great! Let me explain the error situation a bit further. There are two types of filters we use, one is a D.E. filter and the other is a sand filter. The D.E. filter area is called out by FT^2 and the sand filter area is called out by SQ. FT.. Usually each job we do only uses one or the other and so if we're using a D.E. filter then the sand filter doesn't show up in our list. When it's not included then I get the error. It converts almost everything within the list but stops about 3/4 of the way through.

I have included a list from one of our jobs on Sheet2 on the attachment so you can run your code on and hopefully you can see what I'm talking about.

Your conversions are definitely one less step. I actually use the same converter your link takes me to, it's the greatest.

macropod · #11 09-19-2014, 04:20 PM

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.

Carchee · #12 09-22-2014, 11:02 AM

You are correct in that we don't convert the MNPT call out. Thank you for the advise we will change something in our call out so it doesn't convert. Also the SF stands for Service Factor and it's electrical info for the pump motors, but we don't convert it to anything so it's fine. We also don't convert the 15G/HR.

The addition of the rounding off as orders and magnitudes increase is something that we do, so it was nice that you added it. Thanks.

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

macropod · #13 09-23-2014, 10:49 PM

Quote:

Originally Posted by Carchee

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

Carchee · #14 09-22-2014, 11:23 AM

I appreciate your help with this code. I have found that when I 'X' out or delete some of the information in our list the code works fine. If you recall I was getting an error, those were only happening when nothing was being X'd out or deleted. So I would like to send you a list of the equipment that we use which is somewhat proprietary.

Carchee · #15 09-24-2014, 03:45 PM

The DN numbers are crazy and don't make sense to me either, however it is an International Organization for Standardization (ISO) standard and so I just go along with it

One note to make is the last three items on the lists (Pressure and Compound Gauges, Vacuum Switch) will not need any type of conversion, they are constants and the sizes never change. This is why I didn't include them in the original lists, but then I thought I needed to include everything so we can see if they create any errors. Is there a way to omit code from altering those last three lines? This also means that the PSI conversion isn't needed.

I'm still getting the error on the list contained on Sheet2 that I sent you. That's the error I'm not sure why it's happening and can't figure it out. But I did find what contents is making it crash. It's the "7 Gal" in the pH FEED PUMP specifications line. When I delete that or move it so it's not the last two words in the statement I don't get an error. But no matter where I move it within the statement it won't convert.

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
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