On Sat, 16 Oct 2010 13:16:15 -0500, "JCO"
wrote:

I need a macro that allows me to select a column (or drag the selection),
then format the telephone numbers with my specific format (xxx.xxx.xxxx).

The problem is that the column may already have any of these following
formats (requiring a change)
(xxx) xxx-xxxx
xxx-xxx-xxxx
xxx.xxx.xxxx

Obviously the last format is what I want so that means some numbers are
already okay.
Is this possible to do?
Thanks


If you select a cell in some column, the macro will expand to include
all of the cells in that column. It will then check each cell and, if
the cell contains 7 or 10 digits, it will convert it to a phone number
in the format you specified.

============================
Option Explicit
Sub PhoneNums()
Dim rg As Range, c As Range
Dim lPhoneNumCol As Long
Dim re As Object, mc As Object
lPhoneNumCol = Selection.Column
Set rg = Range(Cells(1, lPhoneNumCol), _
Cells(Cells.Rows.Count, lPhoneNumCol).End(xlUp))
Set re = CreateObject("vbscript.regexp")
re.Global = True
For Each c In rg
With c
re.Pattern = "\d" 'numbers
Set mc = re.Execute(.Text)
'Is the cell a phone number?
If mc.Count = 7 Or mc.Count = 10 Then
re.Pattern = "\D+" 'remove non-numbers
.Value = re.Replace(.Text, "")
.NumberFormat = _
"[9999999]000\.000\.0000;000\.0000"
End If
End With
Next c
End Sub
===============================