Reformat column of telephone numbers

[Maree]

I have a variety of formats in the one column of phone numbers. Some have
parentheses, some do not. I would like them to be all formatted the same way.
It doesn't work through format the cell, special. The other resolution
given assumes all formats are the same. What do I do with the parentheses on
some of the entries?
--
Maree

[Sloth]

you will need to delete the extra charecters so you can format them all the
same.

Select Edit->Find
Click Replace
In the Find box insert an open parenthesis (
Leave the replace box empty
Click Replace all

Repeat the procedure for the following charecters
) (close parenthesis)
- (dash)
(space bar)

you should now have a list of numbers, which can be formated anyway you want.
For example you can use a custom format of

(###) ###-####

to display
1234567890
as
(123) 456-7890

You will need to do this because if the number is entered with parenthesis
to begin with, it is treated as text instead of a number. And any custom
number format won't be applide.

"Maree" wrote:

> I have a variety of formats in the one column of phone numbers. Some have
> parentheses, some do not. I would like them to be all formatted the same way.
> It doesn't work through format the cell, special. The other resolution
> given assumes all formats are the same. What do I do with the parentheses on
> some of the entries?
> --
> Maree

[bpeltzer]

I'd create a helper column that converted all the inputs to numeric values,
than apply the special formatting. If your phone numbers are in column A,
starting in row 2, then enter in B2:
=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"(",""),")",""),"-",""),"
","")). Autofill that formula through column B. Then apply your format and
copy / paste special values to lock in the values in column B.
HTH. --Bruce

"Maree" wrote:

> I have a variety of formats in the one column of phone numbers. Some have
> parentheses, some do not. I would like them to be all formatted the same way.
> It doesn't work through format the cell, special. The other resolution
> given assumes all formats are the same. What do I do with the parentheses on
> some of the entries?
> --
> Maree