This worked great. It messed up on a blank line so I had to select smaller
sections and run it. But it worked great.

Sorry this reply is so late coming. The nntp.aioe.org server would not let
me send out.
Thanks


"Don Guillett Excel MVP" wrote in message
...

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