How do I format phone numbers in excel?
 

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?

How do I format phone numbers in excel?
 

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.


CBickley wrote:

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

How do I format phone numbers in excel?
 

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

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

HTH

"CBickley" wrote:

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?

How do I format phone numbers in excel?
 

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

How do I format phone numbers in excel?
 

EditReplace

what: (
with: nothing
replace all

Same for )


Gord Dibben MS Excel MVP

On Wed, 23 Aug 2006 10:44:01 -0700, CBickley
wrote:

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?

How do I format phone numbers in excel?
 

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" wrote:

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.


CBickley wrote:

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

How do I format phone numbers in excel?
 

Add one more edit|replace

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

CBickley wrote:

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" wrote:

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.


CBickley wrote:

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


--

Dave Peterson

How do I format phone numbers in excel?
 

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

Some data looks like (xxx)xxx-xxxx

Wants it to look like xxx-xxx-xxxx

EditReplace

what: (
with: nothing

EditReplace

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

On Wed, 23 Aug 2006 14:12:51 -0500, Dave Peterson
wrote:

Add one more edit|replace

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

CBickley wrote:

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" wrote:

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.


CBickley wrote:

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


Gord Dibben MS Excel MVP

How do I format phone numbers in excel?
 

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 wrote:

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

Some data looks like (xxx)xxx-xxxx

Wants it to look like xxx-xxx-xxxx

EditReplace

what: (
with: nothing

EditReplace

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

On Wed, 23 Aug 2006 14:12:51 -0500, Dave Peterson
wrote:

Add one more edit|replace

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

CBickley wrote:

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" wrote:

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.


CBickley wrote:

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


Gord Dibben MS Excel MVP

--

Dave Peterson

How do I format phone numbers in excel?
 

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.

Dave Peterson wrote:

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 wrote:

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

Some data looks like (xxx)xxx-xxxx

Wants it to look like xxx-xxx-xxxx

EditReplace

what: (
with: nothing

EditReplace

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

On Wed, 23 Aug 2006 14:12:51 -0500, Dave Peterson
wrote:

Add one more edit|replace

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

CBickley wrote:

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" wrote:

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.


CBickley wrote:

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


Gord Dibben MS Excel MVP

--

Dave Peterson

--

Dave Peterson

How do I format phone numbers in excel?
 

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.




On Wed, 23 Aug 2006 16:24:44 -0500, Dave Peterson
wrote:

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 wrote:

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

Some data looks like (xxx)xxx-xxxx

Wants it to look like xxx-xxx-xxxx

EditReplace

what: (
with: nothing

EditReplace

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

On Wed, 23 Aug 2006 14:12:51 -0500, Dave Peterson
wrote:

Add one more edit|replace

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

CBickley wrote:

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" wrote:

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.


CBickley wrote:

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


Gord Dibben MS Excel MVP

Gord Dibben MS Excel MVP

How do I format phone numbers in excel?
 

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

(Sounds better than reading)

Gord Dibben wrote:

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.

On Wed, 23 Aug 2006 16:24:44 -0500, Dave Peterson
wrote:

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 wrote:

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

Some data looks like (xxx)xxx-xxxx

Wants it to look like xxx-xxx-xxxx

EditReplace

what: (
with: nothing

EditReplace

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

On Wed, 23 Aug 2006 14:12:51 -0500, Dave Peterson
wrote:

Add one more edit|replace

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

CBickley wrote:

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" wrote:

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.


CBickley wrote:

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


Gord Dibben MS Excel MVP

Gord Dibben MS Excel MVP

--

Dave Peterson