Change input in batch

W

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.
 
S

Sean Timmons

Highlight the column, Edit>Replace

find: -
Replace with: (leave this blank)

Replace All

Format column as phone #
 
O

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top