## SUM multiple feet'-inches" entries in one cell

## SUM multiple feet'-inches" entries in one cell

(OP)

I found this code somewhere online. I forget who the author was and apologize but it works very well.

I have a column with several measurements in Feet"-Inch" format. The corresponding cells in the column next to it convert the feet and inches to decimals. At the bottom I have a cell that sums the decimals. I have another cell that converts the total back to feet and inches. What I need to do is have a cell that..

1: converts all lengths to decimals at once

2: sums them

3: converts the total back to feet and inches

Basically I'd like to eliminate the need for the extra column.

Any thoughts?

Thanks,

Doug

Public Function feet(LenString As String)

Dim FootSign As Integer

Dim InchSign As Integer

Dim SpaceSign As Integer

Dim FracSign As Integer

Dim InchString As String

Dim Word2 As String

' Copyright 1999, 2005 MrExcel.com

LenString = Application.WorksheetFunction.Trim(LenString)

'The find function returns an error when the target is not found

'Resume Next will prevent VBA from halting execution.

On Error Resume Next

FootSign = Application.WorksheetFunction.Find("'", LenString)

If IsEmpty(FootSign) Or FootSign = 0 Then

' There are no feet in this expression

feet = 0

FootSign = 0

Else

feet = Val(Left(LenString, FootSign - 1))

End If

' Handle the case where the foot sign is the last character

If Len(LenString) = FootSign Then Exit Function

' Isolate the inch portion of the string

InchString = Application.WorksheetFunction.Trim(Mid(LenString, FootSign + 2))

' Strip off the inch sign, if there is one

InchSign = Application.WorksheetFunction.Find("""", InchString)

If Not IsEmpty(InchSign) Or InchSign = 0 Then

InchString = Application.WorksheetFunction.Trim(Left(InchString, InchSign - 1))

End If

' Do we have two words left, or one?

SpaceSign = Application.WorksheetFunction.Find(" ", InchString)

If IsEmpty(SpaceSign) Or SpaceSign = 0 Then

' There is only one word here. Is it inches or a fraction?

FracSign = Application.WorksheetFunction.Find("/", InchString)

If IsEmpty(FracSign) Or FracSign = 0 Then

'This word is inches

feet = feet + Val(InchString) / 12

Else

' This word is fractional inches

feet = feet + (Val(Left(InchString, FracSign - 1)) / Val(Mid(InchString, FracSign + 1))) / 12

End If

Else

' There are two words here. First word is inches

feet = feet + Val(Left(InchString, SpaceSign - 1)) / 12

' Second word is fractional inches

Word2 = Mid(InchString, SpaceSign + 1)

FracSign = Application.WorksheetFunction.Find("/", Word2)

If IsEmpty(FracSign) Or FracSign = 0 Then

' Return an error

feet = "VALUE!"

Else

If FracSign = 0 Then

feet = "VALUE!"

Else

feet = feet + (Val(Left(Word2, FracSign - 1)) / Val(Mid(Word2, FracSign + 1))) / 12

End If

End If

End If

End Function

Public Function LenText(FeetIn As Double)

' This function will change a decimal number of feet to the text string

' representation of feet, inches, and fractional inches.

' It will round the fractional inches to the nearest 1/x where x is the denominator.

' Copyright 1999 MrExcel.com

Denominator = 32 ' must be 2, 4, 8, 16, 32, 64, 128, etc.

NbrFeet = Fix(FeetIn)

InchIn = (FeetIn - NbrFeet) * 12

NbrInches = Fix(InchIn)

FracIn = (InchIn - NbrInches) * Denominator

Numerator = Application.WorksheetFunction.Round(FracIn, 0)

If Numerator = 0 Then

FracText = ""

ElseIf InchIn >= (11 + (31.4999999 / 32)) Then

NbrFeet = NbrFeet + 1

NbrInches = 0

FracText = ""

ElseIf Numerator = Denominator Then

NbrInches = NbrInches + 1

FracText = ""

Else

Do

' If the numerator is even, divide both numerator and divisor by 2

If Numerator = Application.WorksheetFunction.Even(Numerator) Then

Numerator = Numerator / 2

Denominator = Denominator / 2

Else

FracText = " " & Numerator & "/" & Denominator

Exit Do

End If

Loop

End If

LenText = NbrFeet & "'-" & NbrInches & FracText & """"

End Function

I have a column with several measurements in Feet"-Inch" format. The corresponding cells in the column next to it convert the feet and inches to decimals. At the bottom I have a cell that sums the decimals. I have another cell that converts the total back to feet and inches. What I need to do is have a cell that..

1: converts all lengths to decimals at once

2: sums them

3: converts the total back to feet and inches

Basically I'd like to eliminate the need for the extra column.

Any thoughts?

Thanks,

Doug

Public Function feet(LenString As String)

Dim FootSign As Integer

Dim InchSign As Integer

Dim SpaceSign As Integer

Dim FracSign As Integer

Dim InchString As String

Dim Word2 As String

' Copyright 1999, 2005 MrExcel.com

LenString = Application.WorksheetFunction.Trim(LenString)

'The find function returns an error when the target is not found

'Resume Next will prevent VBA from halting execution.

On Error Resume Next

FootSign = Application.WorksheetFunction.Find("'", LenString)

If IsEmpty(FootSign) Or FootSign = 0 Then

' There are no feet in this expression

feet = 0

FootSign = 0

Else

feet = Val(Left(LenString, FootSign - 1))

End If

' Handle the case where the foot sign is the last character

If Len(LenString) = FootSign Then Exit Function

' Isolate the inch portion of the string

InchString = Application.WorksheetFunction.Trim(Mid(LenString, FootSign + 2))

' Strip off the inch sign, if there is one

InchSign = Application.WorksheetFunction.Find("""", InchString)

If Not IsEmpty(InchSign) Or InchSign = 0 Then

InchString = Application.WorksheetFunction.Trim(Left(InchString, InchSign - 1))

End If

' Do we have two words left, or one?

SpaceSign = Application.WorksheetFunction.Find(" ", InchString)

If IsEmpty(SpaceSign) Or SpaceSign = 0 Then

' There is only one word here. Is it inches or a fraction?

FracSign = Application.WorksheetFunction.Find("/", InchString)

If IsEmpty(FracSign) Or FracSign = 0 Then

'This word is inches

feet = feet + Val(InchString) / 12

Else

' This word is fractional inches

feet = feet + (Val(Left(InchString, FracSign - 1)) / Val(Mid(InchString, FracSign + 1))) / 12

End If

Else

' There are two words here. First word is inches

feet = feet + Val(Left(InchString, SpaceSign - 1)) / 12

' Second word is fractional inches

Word2 = Mid(InchString, SpaceSign + 1)

FracSign = Application.WorksheetFunction.Find("/", Word2)

If IsEmpty(FracSign) Or FracSign = 0 Then

' Return an error

feet = "VALUE!"

Else

If FracSign = 0 Then

feet = "VALUE!"

Else

feet = feet + (Val(Left(Word2, FracSign - 1)) / Val(Mid(Word2, FracSign + 1))) / 12

End If

End If

End If

End Function

Public Function LenText(FeetIn As Double)

' This function will change a decimal number of feet to the text string

' representation of feet, inches, and fractional inches.

' It will round the fractional inches to the nearest 1/x where x is the denominator.

' Copyright 1999 MrExcel.com

Denominator = 32 ' must be 2, 4, 8, 16, 32, 64, 128, etc.

NbrFeet = Fix(FeetIn)

InchIn = (FeetIn - NbrFeet) * 12

NbrInches = Fix(InchIn)

FracIn = (InchIn - NbrInches) * Denominator

Numerator = Application.WorksheetFunction.Round(FracIn, 0)

If Numerator = 0 Then

FracText = ""

ElseIf InchIn >= (11 + (31.4999999 / 32)) Then

NbrFeet = NbrFeet + 1

NbrInches = 0

FracText = ""

ElseIf Numerator = Denominator Then

NbrInches = NbrInches + 1

FracText = ""

Else

Do

' If the numerator is even, divide both numerator and divisor by 2

If Numerator = Application.WorksheetFunction.Even(Numerator) Then

Numerator = Numerator / 2

Denominator = Denominator / 2

Else

FracText = " " & Numerator & "/" & Denominator

Exit Do

End If

Loop

End If

LenText = NbrFeet & "'-" & NbrInches & FracText & """"

End Function

## RE: SUM multiple feet'-inches" entries in one cell

If that's not satisfactory, then sure, you could write a custom function to do the lot. In the Excel Visual Basic Editor go to the "Insert" menu and choose "Module". In the editor that appears, add a function like:

## CODE --> BASIC

Try it out by setting a cell's contents to "=DoCalc()".

Now you just need to change the body of the function to loop through all the cells you want to convert, passing their contents to feet(), accumulating the result, and finally passing the sum to LenText().

## RE: SUM multiple feet'-inches" entries in one cell

Hate to say it but i forgot about this post. Mainly because I took your advice and left it alone. I have a workbook with 10 sheets. Each with 3 columns

of 28 entries. It was kind of a pain originally to set up a data sheet to provide the info to all the sheets but it was already done. I was making a different version of this workbook and didn't want to set up another huge data sheet thus the OP. In the end I set it up the same way.

Another reason I didn't use your approach is....Quite frankly I wouldn't know how to script it! Still in the learning phase and trying to squeeze in the time to

figure out vba. Pluggin away tho!

Thanks again,

Doug

## RE: SUM multiple feet'-inches" entries in one cell

## CODE --> vb

That's using my own functions that convert ft and fractional inches to metric and back again. Converting that to your functions, and assuming you don't need the "denom" argument would be:

## CODE --> vb

If you copy and paste that in any convenient code module (can be the same one as your two existing functions, but doesn't have to be), it should work.

As for whether it is worth doing, I like UDFs, especially simple ones like this because:

- They make the spreadsheet more robust against tampering.

- They don't take long to set up (after a bit of practice).

- Once set up, they can save a lot of time.

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/

## RE: SUM multiple feet'-inches" entries in one cell

When I copied it to the next cell it reads off the first. Do I have to name each range above each total in order for

this to work or am I missing something?

Doug

## RE: SUM multiple feet'-inches" entries in one cell

Amazing how simple it is!

Thanks again,

Doug

## RE: SUM multiple feet'-inches" entries in one cell

## RE: SUM multiple feet'-inches" entries in one cell

That's right, you use it just like the Sum function. There are times when it is useful to name the range the function refers to, but you don't need to, and as you discovered if you copy a function with a named range it still refers to the original range (which is sometimes useful, and sometimes not).

In general, when you write a User Defined Function in VBA you give the input range(s) a name so you can refer to them in the VBA code, but when you use them you can just enter a range address in the same way as a built-in function.

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/

## RE: SUM multiple feet'-inches" entries in one cell

## RE: SUM multiple feet'-inches" entries in one cell

Assuming feet-inch format 4'-6 5/16" and the range to sum A1:A10, type the formula

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

and enter it with ctr-shift-enter keyboard combination to achieve array formula.

It is a long formula in order to eliminate all intermediate values and indexes.

Regards,

Yakpol

## RE: SUM multiple feet'-inches" entries in one cell

But it's good to have the alternative.

I have now added the SumFtInf function to my Units4Excel spreadsheet, which you can download from:

http://newtonexcelbach.wordpress.com/2014/09/05/ex...

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/

## RE: SUM multiple feet'-inches" entries in one cell

Why do a script at all? Here are the contents of two columns, summing the feet and inch columns above...

Cell A8: =SUM(A2:A7)+FLOOR(SUM(B2:B7)/12,1)

Cell B8: =SUM(B2:B7)-12*FLOOR(SUM(B2:B7)/12,1)

--

JHG