• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

formatting help needed

dear all.
Is there any way by which if I type 12045 in a cell , the outcome is 12 ft X 45 inches.

Last three digits should come in inches and if it is 045 than the result displayed should be 45 inches.

Some other examples

12345 should be displayed as 12 ft X 345 inches
23045 should be displayed as 23 ft X 45 inches
23005 should be displayed as 23 ft X 5 inches

thanks and regards
 
dear narayan ,
i want the result to be displayed as 23 ft X 5 inches.
(custom formatting 00 " ft X" 000 "inches" is giving the result
23 ft X 005 inches)
 
Hi ,

That is what I said ; with just cell formatting , it might not be possible to get 23 ft X 5 inches. Using a formula would make it simple.

Narayan
 
A work around way :

A1 =12345
A2 =23045
A3 =23005

Select A1:A3 >> Ctrl+F >>
Find what : 0
Replace with : blank
>> click Replace All >> OK

Become >>

A1 =12345
A2 =2345
A3 =235

Select A1:A3 >>Custom Cell format, Type box enter :

[>9999]0" ft X "000" inches";[>999]0" ft X "00" inches";0" ft X" 0" inches"

Finally,

12345 displayed as 12 ft X 345 inches
2345 displayed as 23 ft X 45 inches
235 displayed as 23 ft X 5 inches

Regards
Bosco
 
Last edited:
Another way to replace the zero in Cell B2 places

=VALUE(SUBSTITUTE(A1,"0","")) copy down, but putting the character formatting that bosco_yip did.

Select A1:A3 >> Format Cell format, Type box enter :

[>9999]0" ft X "000" inches";[>999]0" ft X "00" inches";0" ft X" 0" inches"
 
Back
Top