Formatting Phone Number Data Set

[essex711]

If I have a dataset of phone numbers in excel that are not all in the same
format, is there an easy way to have them reformatted so that they are all
consistent in that coulmn?

 

[Dave Peterson]

Depending on how those phone numbers looked, I'd do a bunch of edit|replaces to
remove any non-numeric characters

Get rid of the ('s, )'s, (spaces), -'s and anything else you see.

Then apply a custom/special number format to the resulting digits.

 

[Dave Peterson]

Depending on how those phone numbers looked, I'd do a bunch of edit|replaces to
remove any non-numeric characters

Get rid of the ('s, )'s, (spaces), -'s and anything else you see.

Then apply a custom/special number format to the resulting digits.

 

[Gord Dibben]

Post a sample of the various formats you currently have.

You will probably have to do some stripping or adding of characters or
spaces.

Examples................

250 248 6616

(250) 248 6616

(250) 248-6616

2502486616

What format would you like those changed to?


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Post a sample of the various formats you currently have.

You will probably have to do some stripping or adding of characters or
spaces.

Examples................

250 248 6616

(250) 248 6616

(250) 248-6616

2502486616

What format would you like those changed to?


Gord Dibben MS Excel MVP

 

[essex711]

I have a range of formats:

(555)945-4646

555-255-6323

555.895.8543

1+555.895.5074

Generalyl I would like to get rid of all spaces, parentheses, period's and
1+, 1-, to have all the numbers appear like so:

555-456-8542

 

[essex711]

I have a range of formats:

(555)945-4646

555-255-6323

555.895.8543

1+555.895.5074

Generalyl I would like to get rid of all spaces, parentheses, period's and
1+, 1-, to have all the numbers appear like so:

555-456-8542

 

[essex711]

Using the "find & replace" tool worked fine, and then I just formatted the
dataset into phone numbers, the only issue with that is that there are
international numbers and some datasets are for pager numbers which include
email addresses. Not sure if anything can be done about that.

 

[essex711]

Using the "find & replace" tool worked fine, and then I just formatted the
dataset into phone numbers, the only issue with that is that there are
international numbers and some datasets are for pager numbers which include
email addresses. Not sure if anything can be done about that.