CODEWITHSUNDEEP
vbaexceldata-conversion
BWasse
BWasse

Reputation: 33

Converting feet & inches (e.g., "5 ft 1 in") to decimal feet using VBA or Excel

I have a database with a few columns that store height data in the following format...

8 ft 4 in

5 ft 1 in

8 ft 6 in

12 ft 0 in

and so on...for 20k+ records over 3 columns. I need to convert this to decimal feet either using an excel formula or VBA module. I've adapted another VBA script I used to convert coordinates listed in degrees minutes seconds to decimal degrees, and I'm uncertain where I'm 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 totally off as I'm a beginner with VBA. There's certainly something wrong with the script as it is an adaptation from a different script. There may be an excel formula which is easier to use.

Hopefully someone can help. All the other threads include conversions for values which are not formatted the same way as mine. Thank you!

Upvotes: 3

Views: 4065

Answers (4)

pnuts
pnuts

Reputation: 59485

Can cope with inches only (ie without converting them into dates!):

=IFERROR(LEFT(A1,FIND(" ft",A1)),0)+LEFT(RIGHT(A1,5),2)/12

Upvotes: 0

Tehscript
Tehscript

Reputation: 2556

One liner VBA:

Function ConvertFtInches(pInput As String) As Double
ConvertFtInches = Split(pInput, " ft ")(0) + Split(Split(pInput, " ft ")(1), " in")(0) / 12
End Function

Upvotes: 5

Scott Craner
Scott Craner

Reputation: 152585

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

enter image description here

Upvotes: 4

Scott Holtzman
Scott Holtzman

Reputation: 27259

Here's an Excel formula that will work:

=VALUE(LEFT(A1,FIND("ft",A1)-1))+(VALUE(SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND(" ",A1)),"in",""))/12)

enter image description here

Upvotes: 1

Related Questions