On Oct 16, 3:01*pm, James Ravenswood
wrote:
On Oct 16, 2:16*pm, "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

Give this a try:

Sub FixFormat()
Dim r As Range, s As String, n As Double
For Each r In Selection
* * If IsNumeric(r) And Len(r.Value) = 10 Then
* * * * r.NumberFormat = "[<=9999999]###-####;###"".""###"".""####"
* * Else
* * * * s = r.Value
* * * * s = Replace(s, "(", "")
* * * * s = Replace(s, ")", "")
* * * * s = Replace(s, "-", "")
* * * * s = Replace(s, ".", "")
* * * * s = Replace(s, " ", "")
* * * * n = s
* * * * r.Clear
* * * * r.Value = n
* * * * r.NumberFormat = "[<=9999999]###-####;###"".""###"".""####"
* * End If
Next
End Sub

In another post I answered this for you or someone about extracting
the phone numbers. Just add the number fomat line to that

Sub DoPhoneNumbers()
Dim mr As Range
Dim i As Long
Dim c As Range

Set mr = Range("C25:C34")
With mr
..Replace " ", ""
..Replace "-", ""
..Replace ".", ""
..Replace "(", ""
..Replace ")", ""
End With
For Each c In mr
For i = 1 To Len(c)
If Mid(c, i, 1) Like "[1234567890]" And _
Mid(c, 9 + i, 1) Like "[1234567890]" Then
MsgBox Mid(c, i, 10)
c.Offset(, 1) = Mid(c, i, 10)
'add line below
c.Offset(, 1).NumberFormat = "[<=9999999]###-
####;###"".""###"".""####"

Exit For
End If
Next i
Next c
End Sub