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