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