Formula to add feet and inches
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!
Best Answer

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.
Answers

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

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

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.

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

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

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?

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

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?

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!

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.

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

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.
Help Article Resources
Categories
Check out the Formula Handbook template!