Pearson Software Consulting Services

    Working With Feet And Inches In Excel     

         Excel doesn't provide any built in means to work with measurements in feet and inches. It treats a representation of feet and inches such as 5'10" as simply a text string, and as such, cannot do any arithmetic operations like addition and subtraction with it. To work with feet and inches, you need to convert the text string to a numeric value that Excel can use.

The formula below will convert the text string in cell A1 to a decimal value that Excel can use for arithmetic operations:

 =LEFT(A1,FIND("'",A1)-1)+((MID(A1,FIND("'",A1)+1,LEN(A1)-FIND("'",A1)-1))/12)

The input string to this function should not contain spaces or other characters. This formula can be used as an array formula to sum up a range of values:

=SUM(LEFT(A1,FIND("'",A1:A5)-1)+((MID(A1:A5,FIND("'",A1:A5)+1,LEN(A1:A5)-FIND("'",A1:A5)-1))/12))

For a VBA based solution, use the following function:

Function ConvertFeetInches(S As String) As Double
    Dim Pos As Integer
    Pos = InStr(1, S, "'")
    ConvertFeetInches = CDbl(Left(S, Pos - 1)) + _
        CDbl(Mid(S, Pos + 1, Len(S) - Pos - 1)) / 12
End Function
 

See also my page on Fractional Arithmetic.

 

 

 
     
     

 

 Created By Chip Pearson and Pearson Software Consulting, LLC 
This Page:                Updated: November 06, 2013     
MAIN PAGE    About This Site    Consulting    Downloads  
Page Index     Search    Topic Index    What's New    Links   Legalese And Disclaimers
chip@cpearson.com

© Copyright 1997-2007  Charles H. Pearson