format numbers into feet / inches

[rebar]

IS there a macro or formula to enter 10.333' as 10'-4" or 10-04.

 

[Tom Hayakawa]

Well, if you had the 10.3333' in cell A1, you could put this formula in A2:
=CONCATENATE(INT(VALUE(IF(RIGHT(A1,1)="'",LEFT(A1,LEN(A1)-1),A1))),"'
",ROUND(((VALUE(IF(RIGHT(A1,1)="'",LEFT(A1,LEN(A1)-1),A1))-INT(VALUE(IF(RIGHT(A1,1)="'",LEFT(A1,LEN(A1)-1),A1))))*12),0),"""")

If you could lose the "'" at the end of 10.3333 then the formula would only
need to be: =CONCATENATE(INT(A1),"' ",ROUND((A1-INT(A1))*12,0),"""")

This is someone else's technique, and I apologize for not being able to
properly attribute credit to them by coming up with their name, but I am
blanking right now - it's either John Walkenbach or Joseph Rubin.

Good Luck,

Tom Hayakawa

 

[Gord Dibben]

Have a look at Chip Pearson's site for working with feet and inches using the
ATP DOLLARDE and DOLLARFR functions.

http://www.cpearson.com/excel/fractional.htm

Gord Dibben Excel MVP

 

[K.S.Warrier]

hi,
In one way ,it can be done as follows.
cells:- A1(given feet in decimals) B1(converted as total inches)
C1(feet portion only) D1(inches portion only)
In A1=10.333, B1=A1*12 , C1=int(B1/12), D1=mod(B1,12) give values
124 10 4
K.S.Warrier