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