Feet-Inches Text To Number Macro
Feet-Inches Text To Number Macro
(OP)
I get an excel report from 3D modeling software which gives center of gravity coordinates as text i.e. 6'-2". Is there an existing macro to convert this to a number, preferably in inches - 6'-2" becomes 74.





RE: Feet-Inches Text To Number Macro
custom Excel function attached. May be useful to you.
String will be converted to feet.
Thanks
Nitin Patel
"Treat every one with politeness even those who are rude to you, Not because they are not nice but because you are nice........"
Function STOF(FinSTR As String)
FinSTR = Trim(FinSTR)
posF = InStr(1, FinSTR, Chr(39), vbTextCompare) ' ''''''''
posI = InStr(1, FinSTR, Chr(34), vbTextCompare) ' """""""
If Len(FinSTR) > posF And posI = 0 Then ' if no """"""" add it to end of string
FinSTR = FinSTR & Chr(34)
End If
posF = InStr(1, FinSTR, Chr(39), vbTextCompare) ' ''''''''
posI = InStr(1, FinSTR, Chr(34), vbTextCompare) ' """""""
If posF = 0 Then
feetSTR = 0#
Else
feetSTR = Val(Trim(Left(FinSTR, posF - 1)))
End If
inchSTR = Trim(Right(FinSTR, Len(FinSTR) - posF))
If Len(inchSTR) > 0 Then
posS = InStr(1, inchSTR, "/", vbTextCompare)
posB = InStr(1, inchSTR, Chr(32), vbTextCompare)
posD = InStr(1, inchSTR, "-", vbTextCompare)
If posB = 0 And posD = 0 And posS = 0 Then
inchSTR = Val(inchSTR)
End If
If posB = 0 And posD = 0 And posS > 0 Then
posS = InStr(1, inchSTR, "/", vbTextCompare)
inchSTR2A = Trim(Left(inchSTR, posS - 1))
inchSTR2B = Trim(Right(inchSTR, Len(inchSTR) - posS))
inchSTR = Val(inchSTR2A) / Val(inchSTR2B)
End If
If posB > 0 And posS > 0 Then
inchSTR1 = Left(inchSTR, posB - 1)
inchSTR2 = Right(inchSTR, Len(inchSTR) - posB)
posS = InStr(1, inchSTR2, "/", vbTextCompare)
inchSTR2A = Left(inchSTR2, posS - 1)
inchSTR2B = Right(inchSTR2, Len(inchSTR2) - posS)
inchSTR = Val(inchSTR1) + Val(inchSTR2A) / Val(inchSTR2B)
End If
If posD > 0 And posS > 0 Then
inchSTR1 = Left(inchSTR, posD - 1)
inchSTR2 = Right(inchSTR, Len(inchSTR) - posD)
posS = InStr(1, inchSTR2, "/", vbTextCompare)
inchSTR2A = Left(inchSTR2, posS - 1)
inchSTR2B = Right(inchSTR2, Len(inchSTR2) - posS)
inchSTR = Val(inchSTR1) + Val(inchSTR2A) / Val(inchSTR2B)
End If
Else
inchSTR = 0#
End If
STOF = feetSTR + (inchSTR / 12#)
End Function
RE: Feet-Inches Text To Number Macro
Revised function.
Hope this will work
Nitin patel
Function STOF(FinSTR As String)
FinSTR = Trim(FinSTR)
posF = InStr(1, FinSTR, Chr(39), vbTextCompare) ' ''''''''
posI = InStr(1, FinSTR, Chr(34), vbTextCompare) ' """""""
If Len(FinSTR) > posF And posI = 0 Then ' if no """"""" add it to end of string
FinSTR = FinSTR & Chr(34)
End If
posF = InStr(1, FinSTR, Chr(39), vbTextCompare) ' ''''''''
posI = InStr(1, FinSTR, Chr(34), vbTextCompare) ' """""""
If posF = 0 Then
feetstr = 0#
Else
feetstr = Val(Trim(Left(FinSTR, posF - 1)))
End If
If feetstr <> 0 Then
posF = Application.WorksheetFunction.Max(InStr(1, FinSTR, Chr(39), vbTextCompare), InStr(1, FinSTR, "-", vbTextCompare))
inchSTR = Trim(Right(FinSTR, Len(FinSTR) - posF))
Else
inchSTR = Trim(FinSTR)
End If
If Len(inchSTR) > 0 Then
posS = InStr(1, inchSTR, "/", vbTextCompare)
posB = InStr(1, inchSTR, Chr(32), vbTextCompare)
posD = InStr(1, inchSTR, "-", vbTextCompare)
If posB = 0 And posD = 0 And posS = 0 Then
inchSTR = Val(inchSTR)
End If
If posB = 0 And posD = 0 And posS > 0 Then
posS = InStr(1, inchSTR, "/", vbTextCompare)
inchSTR2A = Trim(Left(inchSTR, posS - 1))
inchSTR2B = Trim(Right(inchSTR, Len(inchSTR) - posS))
inchSTR = Val(inchSTR2A) / Val(inchSTR2B)
End If
If posB > 0 And posS > 0 Then
inchSTR1 = Left(inchSTR, posB - 1)
inchSTR2 = Right(inchSTR, Len(inchSTR) - posB)
posS = InStr(1, inchSTR2, "/", vbTextCompare)
inchSTR2A = Left(inchSTR2, posS - 1)
inchSTR2B = Right(inchSTR2, Len(inchSTR2) - posS)
inchSTR = Val(inchSTR1) + Val(inchSTR2A) / Val(inchSTR2B)
End If
If posD > 0 And posS > 0 Then
inchSTR1 = Left(inchSTR, posD - 1)
inchSTR2 = Right(inchSTR, Len(inchSTR) - posD)
posS = InStr(1, inchSTR2, "/", vbTextCompare)
inchSTR2A = Left(inchSTR2, posS - 1)
inchSTR2B = Right(inchSTR2, Len(inchSTR2) - posS)
inchSTR = Val(inchSTR1) + Val(inchSTR2A) / Val(inchSTR2B)
End If
Else
inchSTR = 0#
End If
STOF = feetstr + (inchSTR / 12#)
End Function
RE: Feet-Inches Text To Number Macro
=LEFT(A1,SEARCH("'",A1)-1)*12+MID(A1,SEARCH("-",A1,2)+1,LEN(A1)-SEARCH("-",A1,2)-1)
ft-in text (like 6'-2" or 6'-2 1/8") is in cell A1