Format Telephone Numbers

J

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
 
J

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
Click to expand...




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
 
D

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).
Click to expand...
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
Click to expand...
Obviously the last format is what I want so that means some numbers are
already okay.
Is this possible to do?
Thanks
Click to expand...

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
Click to expand...

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
 
J

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).
Click to expand...
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
Click to expand...
Obviously the last format is what I want so that means some numbers are
already okay.
Is this possible to do?
Thanks
Click to expand...

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
Click to expand...

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
 
J

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
Click to expand...

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

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
Click to expand...

Glad to help. Thanks for the feedback.
 
J

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

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.
Click to expand...

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

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.
Click to expand...

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Outlook 2007 contact form - telephone number auto-formatting 3
Auto-format previously entered phone numbers 4
How can I delete a space/character from a number of fields? 10
ADDRESS MACRO 2
Simple Problems 1
Phone Number Display formatting 1
Put parentheses around figures 8
Find specific text in a string 5

Top