Feet and Inches

Hi,
I need to convert inches to feet and inches in this format:
88 1/2 = 7' 4-1/2"
...so that if 88 1/2 is in cell A1, cell B1 will show 7' 4-1/2".
The exact syntax of B1 must be as shown.
Thanks

I couldnt get the syntax to match your request exactly. Hopefully, someone else can expound.

If your inches are in A1, enter this formula into B!:

=INT(A1/12)&"' "&MOD(A1,12)&""""

Possibly amend BigBas' suggestion to

=INT(A1/12)&"' "&TEXT(MOD(A1,12),"0 #/#")&""""

Thx, daddy. I assumed someone would fix it using custom number formatting (which I am still not to great at).

O.K.
...Works Great!!! Thanks so much- what a neat group.
Ya'll (<I'm from Arkansas) saved the day.

This may sound crazy but I have the exact *opposite* problem...I have a measurement typed in text (for example 6-3 for 6 foot three inches) and need something that takes this text string and converts it to a number in inches (in this case 75 inches). Any help?

Originally Posted by Bryan Hessey

Hi,

=1*(LEFT(A1,FIND("-",A1)-1)*12+MID(A1,FIND("-",A1)+1,2))

will do that, the 1* I added solely because my A1 was 'Text' format and I didn't want the answer as text.

hth
---

EXCELLENT! Thanks so much!

I'm back again with another small (I hope) question/alternation needed to the formula above...what if i had a half inch in the measurement? For example 6-3 1/2...it would be written exactly like this (6-3 1/2) with the space and the fraction written like this (1/2) I need the forumla to kick out an answer of 75.5...thanks for any help at all!

Originally Posted by Bryan Hessey

Hi,

=(1*(LEFT(A1,FIND("-",A1)-1)*12+MID(A1,FIND("-",A1)+1,2)))+IF(LEN(A1)=LEN(SUBSTITUTE(A1,"1/2","")),0,0.5)


looks dodgy but will do (only) 1/2 additions.

note, you could also add 1/4 and 3/4 this way, but other fractions would need more care.

hth
---

Couldn't you close that statment with a

+IF(RIGHT(A1,2)="/",1*RIGHT(A1,2),0)

I didnt try it out, but it should work, no?

Good point. I almost forgot those existed. =)

You can just tweak Bryan's original to....

=LEFT(A1,FIND("-",A1)-1)*12+MID(A1,FIND("-",A1)+1,255)

=LEFT(A1,FIND("-",A1)-1)*12 + RIGHT(A1,(LEN(A1)-FIND("-",A1)))

If You are getting help elsewhere please provide a link. We have been working on the same problem at ozgrid.

See Here