Format Telephone Numbers
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
|