I have a long list of phone numbers that were input as text in the following
format: xxx-xxx-xxxx. If I change the cell format to "cutom" and "phone",
under edit cells, then remove the hyphens, I end up with (xxx) xxx-xxxx. This
is what I want; however cannot figure out hot to write a macro or otherwise
to do it automatically.

Highlight the column, EditReplace

find: -
Replace with: (leave this blank)

Replace All

Format column as phone #

"WarrenL" wrote:

I have a long list of phone numbers that were input as text in the following
format: xxx-xxx-xxxx. If I change the cell format to "cutom" and "phone",
under edit cells, then remove the hyphens, I end up with (xxx) xxx-xxxx. This
is what I want; however cannot figure out hot to write a macro or otherwise
to do it automatically.

Warren
This little macro should do what you want. I assumed that your phone
numbers are in Column A starting in A2. Change this as needed. This macro
will change both 123-456-7891 and 1234567891 to (123) 456-7891. HTH Otto
Sub PhoneNum()
Dim rColA As Range, i As Range
Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
For Each i In rColA
i = Replace(i, "-", "")
i.NumberFormat = "[<=9999999]###-####;(###) ###-####"
Next i
End Sub
"WarrenL" wrote in message
...
I have a long list of phone numbers that were input as text in the
following
format: xxx-xxx-xxxx. If I change the cell format to "cutom" and "phone",
under edit cells, then remove the hyphens, I end up with (xxx) xxx-xxxx.
This
is what I want; however cannot figure out hot to write a macro or
otherwise
to do it automatically.