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

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

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

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

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

Awesome.. that works great. thanks so much


"Ron Rosenfeld" wrote in message
...

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

On Thu, 28 Oct 2010 15:13:41 -0500, "JCO"
wrote:

Awesome.. that works great. thanks so much

Glad to help. Thanks for the feedback.

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

On Thu, 28 Oct 2010 15:13:41 -0500, "JCO"
wrote:

Awesome.. that works great. thanks so much

Glad to help. Thanks for the feedback.

On Tue, 2 Nov 2010 13:05:47 -0500, "JCO"
wrote:

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.

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" wrote in message
...
On Tue, 2 Nov 2010 13:05:47 -0500, "JCO"
wrote:

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.

On Fri, 12 Nov 2010 16:35:59 -0600, "JCO"
wrote:

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

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

On Fri, 12 Nov 2010 16:35:59 -0600, "JCO"
wrote:

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

On Thu, 18 Nov 2010 17:27:11 -0600, "JCO"
wrote:

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.