Format Telephone Numbers

[JCO]

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

 

[James Ravenswood]

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

 

[Don Guillett Excel MVP]

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

 

[Ron Rosenfeld]

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
===============================

 

[JCO]

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

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

 

[JCO]

Awesome.. that works great. thanks so much


"Ron Rosenfeld" wrote in message

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
===============================

 

[Ron Rosenfeld]

Awesome.. that works great. thanks so much

Glad to help. Thanks for the feedback.

 

[JCO]

Okay I've ran into an unexpected issue.
I used the macro to change all phone number to format xxx.xxx.xxxx
When I put the mouse on the cell and look in the Formula Bar, the number
shows up as xxxxxxxxxx (no decimals). This is the case with all numbers and
I'm not sure how this will impact me later. Why is this the case?
Thanks

"Ron Rosenfeld" wrote in message

Awesome.. that works great. thanks so much

Glad to help. Thanks for the feedback.

 

[Ron Rosenfeld]

Okay I've ran into an unexpected issue.
I used the macro to change all phone number to format xxx.xxx.xxxx
When I put the mouse on the cell and look in the Formula Bar, the number
shows up as xxxxxxxxxx (no decimals). This is the case with all numbers and
I'm not sure how this will impact me later. Why is this the case?
Thanks

That is by design. The phone numbers are stored as "numbers" and then
formatted to appear the way you specified. They could be stored as
text strings, but this might make things more difficult in the future.

This method allows more flexibility in changing the format between
programs, or even between different worksheets/workbooks in Excel. Or
even if you subsequently decide you want to use a different format.

It does require, however, that if you import the data into another
program (or mail merge), that you have to specify the formatting you
want in that other program.

 

[JCO]

Sorry it took so long... I've been vacationing in Mexico for the past week.
I read your reply. So what do I have to do to have it appear in Text
format? I actually think the text field is preferred in my case.

 

[Ron Rosenfeld]

Sorry it took so long... I've been vacationing in Mexico for the past week.
I read your reply. So what do I have to do to have it appear in Text
format? I actually think the text field is preferred in my case.

Just output it as a formatted text string.

So instead of:

==========================
.Value = re.Replace(.Text, "")
.NumberFormat = _
"[>9999999]000\.000\.0000;000\.0000"
============================

You'd have something like (not tested):

==============================
..Value = Format(re.replace(.text,""), _
"[>9999999]000\.000\.0000;000\.0000"
===============================

 

[JCO]

This actually has a compile error and I'm sorry, I don't know enough to fix
it.
Thanks so far for all you help and patients.

"Ron Rosenfeld" wrote in message

Sorry it took so long... I've been vacationing in Mexico for the past week.
I read your reply. So what do I have to do to have it appear in Text
format? I actually think the text field is preferred in my case.

Just output it as a formatted text string.

So instead of:

==========================
..Value = re.Replace(.Text, "")
..NumberFormat = _
"[>9999999]000\.000\.0000;000\.0000"
============================

You'd have something like (not tested):

==============================
..Value = Format(re.replace(.text,""), _
"[>9999999]000\.000\.0000;000\.0000"
===============================

 

[Ron Rosenfeld]

This actually has a compile error and I'm sorry, I don't know enough to fix
it.
Thanks so far for all you help and patients.

You could start by posting a copy of the code that is causing the
compile error. Post your entire macro.

 

[JCO]

Code is shown below. I resolved the compile error however, it still does
the same thing (phone format instead of text).
Code below:
Sub FormatPhoneNums()
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
**************************************

"Ron Rosenfeld" wrote in message

This actually has a compile error and I'm sorry, I don't know enough to fix
it.
Thanks so far for all you help and patients.

You could start by posting a copy of the code that is causing the
compile error. Post your entire macro.

 

[Ron Rosenfeld]

Code is shown below. I resolved the compile error however, it still does
the same thing (phone format instead of text).
Code below:
Sub FormatPhoneNums()
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
**************************************

That is because you did not change the output from numeric to text as
I suggested in my previous message:

(note the change in the .Value line)

============================
Option Explicit
Sub FormatPhoneNums()
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 = Format(re.Replace(.Text, ""), _
"[>9999999]000\.000\.0000;000\.0000")
End If
End With
Next c
End Sub
================================