Formula to add feet and inches

noclue

Is there a formula that can add feet and inches together?

For example, I need to add all these figure together to get one number in feet and inches.

346' 11"

349' 7"

346' 2"

342' 4"

351' 6"

357' 9"

380'

I may need to convert it all to inches first. I am using this formula but I cannot get the right side of the formula to work and I am not sure why.

=VALUE(LEFT(Width@row, FIND("'", Width@row) - 1)) * 12 + VALUE(RIGHT(SUBSTITUTE(Width@row, """, ""), LEN(Width@row) - FIND(" ", Width@row)))

Thank you!

Paul Newcome

Good deal! Happy to help! 👍️

Please don't forget to mark the most appropriate response(s) as "helpful" so that others searching for a similar solution can know that one may be found here.

Paul Newcome

What are the chances one or more of the measurements will be just inches?

noclue

Hello Paul, it should never be in just inches. Thank you.

Paul Newcome

Ok. Good. Because that was the part that was throwing me the most. Haha. Let me finish up some testing, and I'll get back to you.

Paul Newcome

Ok. You are going to need a helper column where we convert everything from a text string to a numerical value. I converted everything to inches by using the below in each row:

=(VALUE(LEFT(Width@row, FIND("'", Width@row) - 1)) * 12) + VALUE(IF(FIND("' ", Width@row) > 0, MID(Width@row, FIND(" ", Width@row) + 1, LEN(Width@row) - (FIND(" ", Width@row) + 1)), 0))

Then to get the SUM and convert it back to ###' ##", I used:

=INT(SUM(Inches:Inches) / 12) + "' " + MOD(SUM(Inches:Inches), 12) + "''"

NOTE: The end of the formula is actually quote/apostrophe/apostrophe/quote

noclue

Thank you! The first part worked but I'm getting a Value error when I try to convert back to ###' ##"

noclue

If I just do a sum of all the inches first, is there a formula that would convert just the sum of inches back to ###' ##"? Would that be easier?

noclue

OK NEVER MIND! I got it to work. Thank you for your help!

Paul Newcome

Can you copy/paste the exact formulas from the sheet?

Did you see the note regarding the end of the second formula?

What is the exact error you are receiving?

noclue

I got it to work using your formula as is. I'm not sure why I got an error the first time. But thank you!

Paul Newcome

Good deal! Happy to help! 👍️

Please don't forget to mark the most appropriate response(s) as "helpful" so that others searching for a similar solution can know that one may be found here.

noclue

Sorry, me again. Can I modify this to not give me an error if there are no inches on some?

Paul Newcome

I'm not sure what you mean. When I did my testing I had an entry with inches and without and didn't get an error.