how to input foot and inches and make it operational

**gcastilleja** · 10-09-2008, 04:38 PM

Hello again Guys, i'm bothering you this time because i need to make that excel recognize 4' 2" times 24. I have in A1 4' 2" and in B1 24, but when i do the operation in c1 =a1*b1 it would return me an error message (#value) i guess this is because the numbers may be stored as text.
how do i make excel to recognize them as numbers?
what formula can help me in multipy foot and inches times full numbers. I hope you can save me again. Thank you very much mis amigos.

**Paul** · 10-09-2008, 09:42 PM

Hi Gcastilleja,

To return the number of feet and inches (in decimal, e.g. 100.25) try this formula in C1:

=(B1*VALUE(LEFT(A1,FIND("'",A1)-1)))+(B1/12*VALUE(MID(A1,FIND("'",A1)+1,FIND("""",A1)-(FIND("'",A1)+1))))

If you want to return it to x' yy" again, without using helper columns, this should work:

=INT((B1*VALUE(LEFT(A1,FIND("'",A1)-1)))+(B1/12*VALUE(MID(A1,FIND("'",A1)+1,FIND("""",A1)-(FIND("'",A1)+1)))))&"' "&ROUND(((B1*VALUE(LEFT(A1,FIND("'",A1)-1)))+(B1/12*VALUE(MID(A1,FIND("'",A1)+1,FIND("""",A1)-(FIND("'",A1)+1))))-INT((B1*VALUE(LEFT(A1,FIND("'",A1)-1)))+(B1/12*VALUE(MID(A1,FIND("'",A1)+1,FIND("""",A1)-(FIND("'",A1)+1))))))*12,0)&""""

I'm sure there's neater formulas out there, but it's what my little brain came up with. VBA could also be used, and I'm pretty sure I've seen examples of doing this somewhere..

Hope that helps!