Feet-inches number format

jlarson · May 3rd, 2002, 10:05 AM

Is it possible to define a custom number format to display lengths such as 6' 2"? I found a custom function on this site that allows me to display decimals in that manner, but I'd prefer to eliminate the functions, as I'm dealing with several thousand lines. Simple text formatting will give me the display I want, but I lose my numerical values (6' 10" sorts as less than 6' 2").

lenze · May 3rd, 2002, 10:41 AM

Use custom format and enter
0"Ft".00"in."
This will allow you to enter feet and inches, but you will have to enter less than 10 inches with a leading zero. 10.06 for 10 ft 6 inches, 5.11 for 5 ft 11 inches. This will allow proper sorting. If you want the display to be 5'.06" then substitute a ' inside the double quotes for ft and two single ' inside the double quotes for in. You might also look at the DOLLARDE and DOLLARFR functions to assist in using your entires in calcualtion.

[ This Message was edited by: lenze on 2002-05-03 10:13 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 3rd, 2002, 10:05 AM	#1
jlarson New Member Join Date: May 2002 Posts: 5	Is it possible to define a custom number format to display lengths such as 6' 2"? I found a custom function on this site that allows me to display decimals in that manner, but I'd prefer to eliminate the functions, as I'm dealing with several thousand lines. Simple text formatting will give me the display I want, but I lose my numerical values (6' 10" sorts as less than 6' 2").

May 3rd, 2002, 10:41 AM	#2
lenze MrExcel MVP Join Date: Feb 2002 Location: Helena, MT Posts: 13,690	Use custom format and enter 0"Ft".00"in." This will allow you to enter feet and inches, but you will have to enter less than 10 inches with a leading zero. 10.06 for 10 ft 6 inches, 5.11 for 5 ft 11 inches. This will allow proper sorting. If you want the display to be 5'.06" then substitute a ' inside the double quotes for ft and two single ' inside the double quotes for in. You might also look at the DOLLARDE and DOLLARFR functions to assist in using your entires in calcualtion. [ This Message was edited by: lenze on 2002-05-03 10:13 ]