Convert feet and inches (like "5 feet 1") to decimal feet using VBA or Excel
I have a multi-column database that stores height data in the following format ...
8 feet 4 inches
5 ft 1 in
8 feet 6 inches
12 ft 0 in
and so on .... for 20k + records of 3 columns. I need to convert this to decimal feet either with an excel formula or in a VBA module. I adapted another VBA script, I used to convert coordinates listed in degrees minutes seconds to decimal degrees and I don't know where I am going wrong ...
Function ConvertFtInches(pInput As String) As Double
'Updateby20140227
Dim xFt As Double
Dim xIn As Double
xFt = Val(Left(pInput, InStr(1, pInput, " ft") - 1))
xIn = Val(Mid(pInput, InStr(1, pInput, " ft") + 2, _
InStr(1, pInput, " in") - InStr(1, pInput, _
" ft") - 2)) / 12
ConvertFtInches = xFt + xIn
End Function
.... I could be completely without me as I start with VBA. Of course there is something wrong with the script, like adapting from another script. An excel formula can be used which is easier to use.
Hope someone can help. All other streams include conversions for values that are not formatted the same as mine. Thank!
source to share
If you want vba:
Function ConvertFtInches(pInput As Range) As Double
Dim str As String
Dim strArr() As String
str = Replace(pInput.Value, "ft", " ")
str = Replace(str, "in", "")
strArr = Split(Application.Trim(str), " ")
If LBound(strArr) = UBound(strArr) Then
ConvertFtInches = strArr(0) / 12
Else
ConvertFtInches = strArr(0) + strArr(1) / 12
End If
End Function
source to share
