formatting help needed

[paramnayak]

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

 

[NARAYANK991]

Hi ,

I am not sure that 23005 will display as 23 ft X 5 inches ; is 23 ft X 005 inches acceptable ?

Narayan

 

[paramnayak]

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)

 

[NARAYANK991]

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

 

[bosco_yip]

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

 

[deciog]

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"