Results 1 to 2 of 2
2005-03-09, 19:35 #1
- Join Date
- Jun 2001
- Vancouver, BC
- Thanked 0 Times in 0 Posts
Phone Number Format (Excell 2000 SP3)
Our work database stores and displays phone numbers as just 10 digits. I regularly do customer queries that get dumped into a CSV file which I then use in Excel. Once in Excel, I want to display the phone numbers in a more easily readable format, like (212) 555-1212.
In the Format Cells tool, there is a pre-defined format for that in the Special category. However, it doesn't seem to work in a way that would be useful for me. If I format an empty cell to be a telephone number, and then type 10 digits in it, it will display as a phone number like above.
However, if I have existing data, I can't convert it. I'll highlight hundreds of cells with only the 10 digits in them and then select format cells. The cells are apparently in the "General" format. If I choose the Special - Phone Number format, nothing happens; they just stay as undifferentiated 10 digits. (The sample box above the formats just shows 10 digits, nothing else.)
Copying them into blank cells that have been formatted as Special - Phone Number doesn't work either. The only way I can convert them is to use a concatenate formula where I manually add the parentheses and dash - this can't be the most effective way to do it.
Am I missing something here?
Thanks for any help!
2005-03-09, 19:47 #2
- Join Date
- Mar 2002
- Thanked 28 Times in 28 Posts
Re: Phone Number Format (Excell 2000 SP3)
The phone number format only works with numeric data. I suspect that your existing data are text values that look like numbers. Once you convert them to numbers, they should be displayed using the phone number format.
There are several ways to convert text values to numbers:
- Select a (single) column, then select Data | Text to Columns..., make sure Delimited is selected, then click OK.
- Select an empty cell, copy it to the clipboard, select a range of "text numbers", select Edit | Paste Special..., click Add, then OK.