Change input in batch

[WarrenL]

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.

 

[Sean Timmons]

Highlight the column, Edit>Replace

find: -
Replace with: (leave this blank)

Replace All

Format column as phone #

 

[Otto Moehrbach]

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