Phone number formats

I just got an address list with phone numbers in different formats: (000)
000-0000 and 000-000-0000. I want to convert them all to one format. The
format cells way does not seem to work and the search in this archive hasn't
yielded any solutions. Can you take an existing list like this and convert
it to a standard format? Thanks ahead for your help.

Bill

It's probably text, use edit>replace and replace - with nothing and then do
it again and replace the parenthises with nothing, then use the special phone
format

or just replace the right parenthesis with - and the left with nothing and
you will still have text format but it will be the same format


Regards,

Peo Sjoblom

"Bill Clark" wrote:

> I just got an address list with phone numbers in different formats: (000)
> 000-0000 and 000-000-0000. I want to convert them all to one format. The
> format cells way does not seem to work and the search in this archive hasn't
> yielded any solutions. Can you take an existing list like this and convert
> it to a standard format? Thanks ahead for your help.
>
> Bill

There are a dozen ways to accomplish this, but I would just do some old fashioned data cleansing by using your Ctrl+H function.

Replace the unwanted characters (ie: an open parenthasis or a space) with NOTHING (in other words, leave the "replace with" field empty) until all you have left are numbers.

This should take you all of 2 minutes, repeating the function 4 times (assuming all you have are unwanted spaces, dashes and paren's).

Then, just format the row containing raw numbers as a phone number.

Bill

Custom Format as (000)-000-0000

Or Edit>Replace the ( & ) to get 000-000-0000

Your choice.


Gord Dibben Excel MVP

On Wed, 12 Jan 2005 12:49:02 -0800, "Bill Clark"
<[email protected]> wrote:

>I just got an address list with phone numbers in different formats: (000)
>000-0000 and 000-000-0000. I want to convert them all to one format. The
>format cells way does not seem to work and the search in this archive hasn't
>yielded any solutions. Can you take an existing list like this and convert
>it to a standard format? Thanks ahead for your help.
>
>Bill