Newbie Phone Format Problem

[Guest]

I'm a db guy so probably missing something very simple here.
Need to format a column with phone numbers 5555551212 into phone format
555-555-1212.

Have tried to select both the complete column and individual columns and
apply the special phone format and also Custom ###-###-#### but neither one
works on this spreadsheet.

What am I missing.

Thanks for any light you can shed on this for me.

 

[Niek Otten]

Your cells are probably text.

Easy to cure:

Format an empty cell as number
Edit>Copy
Select your phone numbers
Edit>Paste special, check Add

Now you can format them with the special format

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| I'm a db guy so probably missing something very simple here.
| Need to format a column with phone numbers 5555551212 into phone format
| 555-555-1212.
|
| Have tried to select both the complete column and individual columns and
| apply the special phone format and also Custom ###-###-#### but neither one
| works on this spreadsheet.
|
| What am I missing.
|
| Thanks for any light you can shed on this for me.

 

[Guest]

Go into format cells and under the number tab you can choose special. It
comes out as (555) 555-1212. Or you can select custom and type in whatever
format you want. I do this by right clicking at the top of the column. Hope
it works for ya!

 

[Guest]

I have done what you suggested on both a column select and an individual cell
select. I have selected custom and used ###-###-#### but nothing happens?

 

[Gord Dibben]

It is possible the phone numbers are entered or pasted in as text format.

In that case Custom Formatting or Phone formatting won't take.

You can format all to General then copy an empty cell.

Select the column of phone "numbers" and paste special>add>ok>esc.

Note: if any have leading zeros the zeros will be dropped.

Alternate method...Data>Text to Columns>Next>Next>Column Data
Format>General>Finish.


Gord Dibben MS Excel MVP

 

[Guest]

I am really feeling dumb here but I have tried the following
First I am working on a 2000 xls spreadsheet on a terminal server.
The 12,000 phone numbers I have are in column I. I went to column J and
selected the complete column. I then formatted that column as number.

I selected column I and did a copy. I slected column J and did a copy paste
special.
I moved off the column and then came back and selected the complete column J
again and attempted to format as custom ###-###-#### which did not work. I
then tried to formart as special phone number and that did not work.

Wondering if this was a problem with working on a terminal server I sent a
copy to my local email and put this on my local desktop where I have Excell
2003

I attempted to do this again without any sucess. I do notice that there is
a green flag in front of all my values on both the Original column and the
new column?

Like I said feeling pretty dumb. Appreciate the help so far and look
forward to resolving this issue. I need to turn this in by end of business
today.

 

[Guest]

SUCCESS!!!

Using the Data, Text to Columns suggestion worked for me.

Thanks to all