format numbers into feet / inches
IS there a macro or formula to enter 10.333' as 10'-4" or 10-04.
|
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 "rebar" wrote: IS there a macro or formula to enter 10.333' as 10'-4" or 10-04. |
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 On Wed, 3 Nov 2004 13:06:03 -0800, "rebar" wrote: IS there a macro or formula to enter 10.333' as 10'-4" or 10-04. |
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 "Gord Dibben" wrote: 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 On Wed, 3 Nov 2004 13:06:03 -0800, "rebar" wrote: IS there a macro or formula to enter 10.333' as 10'-4" or 10-04. |
All times are GMT +1. The time now is 02:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com