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