Excel 1st Integer 0 Drop 1

[mgilbertson]

I have the following formula to turn a decimal number into feet-inches-16ths. My only problem is I would like to have the leading zero not appear if it is only inches-16th.

Formula: =IF(SIGN(D3)=-1,"- ","")&INT(ROUND(ABS(D3)*16,0)/16/12)&"'-"&TEXT((MOD(ROUND(ABS(D3)*16,0)/16,12)),"# #/##")&""""

Example:
3.5 = 0'-3 1/2" I would rather see 3 1/2"

Example:
13.5 = 1'-1 1/2" This would be the only time the first integer would be required, if it is larger than 0

Can the above formula be modified to drop the first integer if it is 0?

Thank you very much.

 

[bluedragon2]

Add an embedded if that checks to see if the number is less then 12. If so, "", if not, <formula>

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[zelgar]

=IF(SIGN(D3)=-1,"- ","")&IF(ABS(D3)>12,INT(ROUND(ABS(D3)*16,0)/16/12)&"'-"&TEXT((MOD(ROUND(ABS(D3)*16,0)/16,12)),"# #/##")&"""",TEXT((MOD(ROUND(ABS(D3)*16,0)/16,12)),"# #/##")&"""")

 

[mgilbertson]

zelgar

That worked perfectly.

Thank you very much!!!

 

[zelgar]

I made a slight error, It should include an equal sign "=" with the 2nd IF statement (in case where the value is 12).
=IF(SIGN(D3)=-1,"- ","")&IF(ABS(D3)=>12,INT(ROUND(ABS(D3)*16,0)/16/12)&"'-"&TEXT((MOD(ROUND(ABS(D3)*16,0)/16,12)),"# #/##")&"""",TEXT((MOD(ROUND(ABS(D3)*16,0)/16,12)),"# #/##")&"""")