How do I format phone numbers in excel?

[Guest]

I have an excel file with a column for phone numbers. Some are (xxx)
xxx-xxxx and others are xxx-xxx-xxxx. I need to make all the numbers follow
the xxx-xxx-xxxx format but am unable to do so with the format cells. What
do I need to do?

 

[Dave Peterson]

If those () and -'s are really part of the cell (not formatting choices),

Then select the column
and do 3 edit|replaces

edit|replace
what: (
with: (leave blank)
replace all

edit|replace
what: )
with: (leave blank)
replace all

edit|replace
what: -
with: (leave blank)
replace all

Now all your entries should be real numbers and your format|cells|number tab
choices should work ok.

 

[Guest]

If 'phone number in column A, then put this in B and copy down:

=SUBSTITUTE(SUBSTITUTE(A1,"(",""),")","")

HTH

 

[kfogle]

Format Cells > Choose "Custom" Category > In the "Type:" field on the
right type in "000-000-0000"

 

[Gord Dibben]

Edit>Replace

what: (
with: nothing
replace all

Same for )


Gord Dibben MS Excel MVP

 

[Guest]

Thanks, this worked. Now I have a space in the middle of the numbers that I
need to delete (xxx xxxxxxx). How do I do this?

 

[Dave Peterson]

Add one more edit|replace

Edit|Replace
what: (spacebar)
with: (leave blank)
replace all

 

[Gord Dibben]

I'm not sure why OP needs any more than two edit>replaces to get rid of the ()

Some data looks like (xxx)xxx-xxxx

Wants it to look like xxx-xxx-xxxx

Edit>Replace

what: (
with: nothing

Edit>Replace

what: )
with: - My first post was replace with nothing which was incorrect.

returns xxx-xxx-xxxx

I guess I don't understand why the need to trun the phone number into a number
just to turn around and custom format as

xxx-xxx-xxxx


Gord

Add one more edit|replace

Edit|Replace
what: (spacebar)
with: (leave blank)
replace all

Gord Dibben MS Excel MVP

 

[Dave Peterson]

I think that there was an unfortunate line break in the middle of his examples:

I have an excel file with a column for phone numbers. Some are (xxx)
xxx-xxxx and others are xxx-xxx-xxxx.

I think the first example was really:
(xxx) xxx-xxxx
So there would have been an extra space in that string.

And as a personal choice, I'd want all my data the same format--either all text
or all numbers (nicely formatted).

I'd convert any text strings xxx-xxx-xxxx to its number and then use
format|cells to give the whole range a nice consistent look.

 

[Dave Peterson]

And by having the format of the field consistent, I can use =vlookup() and
=match() without having to worry about if the data is text or numeric.

 

[Gord Dibben]

Not seeing the line break, but sure enough could be there.

Now I'm all warm and fuzzy again and can quit stressing myself over sh.. like
this.

I think that there was an unfortunate line break in the middle of his examples:

I have an excel file with a column for phone numbers. Some are (xxx)
xxx-xxxx and others are xxx-xxx-xxxx.

I think the first example was really:
(xxx) xxx-xxxx
So there would have been an extra space in that string.

And as a personal choice, I'd want all my data the same format--either all text
or all numbers (nicely formatted).

I'd convert any text strings xxx-xxx-xxxx to its number and then use
format|cells to give the whole range a nice consistent look.

Gord Dibben MS Excel MVP

 

[Dave Peterson]

When life gets too tense, reduce it to one fifth!

(Sounds better than reading)