MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Formatting numbers

Posted by Fred Berry on January 02, 2002 11:14 AM

I have a spreadsheet set up that has phone numbers and zip codes. Can anyone tell me how I can get these numbers to display in a standard format. They currently display as a continuous strand(ie 0000000000). I would like them to display as 000-000-0000(phone) or 00000-0000(zip). If possible I would like to format them without having to manually enter the hyphens for each number. I have tried a customized format and could not get it to work. Thanks for your help.

Posted by Rick E. on January 02, 2002 11:30 AM

Fred, if you are using Excel 2000, under Format/Cells there is a Special catagory which has both of those formats defined.

If not then Custom and type your own, for SSN use "000-00-0000" the zeros are used for numbers that you want showing and for zip code use "00000-0000"

Rick E.

Posted by lenze on January 02, 2002 11:36 AM

In Excel 2000, this is is standard format under Format>Cells>Number Tab. Choose Special under Category. I don't recall if this is available in earlier versions. If not, the custom format is

Phone: {>9999999](000)000-0000;000-0000

Zip: 00000-0000


Posted by Scott on January 02, 2002 11:37 AM

Same in Excel 97 (NT)