Feet-Inches Text To Number Macro 1

[PMarinshaw]

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.

 

[nitin36537]

Dear PMarinshaw

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

 

[nitin36537]

Slight modification required to suite your input.
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

 

[cdi12]

Try this one:

=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