Solved

Need VBA code to auto format phone numbers by removing all periods, spaces and dashes and then formatting it

Posted on 2014-10-28
6
202 Views
Last Modified: 2014-10-28
When I copy one of the below phone numbers into cell B7 on sheet1, I need it to resolve to (555) 555-5555

Examples
555-555-5555
(555)-555-5555
555 555 5555
555.555.5555
0
Comment
Question by:kbay808
  • 3
  • 3
6 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40408958
This EE question has two viable solutions for you.  zorvek (Kevin Jones) uses a subroutine that updates a highlighted selection; Patrick Matthews uses regular expressions in a user-defined function.

As a new user of regular expressions, I like Patrick's solution.  Here's the code without the comments included:
Function RegExpReplace(LookIn As String, PatternStr As String, Optional ReplaceWith As String = "", _
     Optional ReplaceAll As Boolean = True, Optional MatchCase As Boolean = True)
     
    Dim RegX As Object
     
    Set RegX = CreateObject("VBScript.RegExp")
     With RegX
         .Pattern = PatternStr
         .Global = ReplaceAll
         .IgnoreCase = Not MatchCase
     End With
     
    RegExpReplace = RegX.Replace(LookIn, ReplaceWith)
    Set RegX = Nothing     
End Function

Open in new window


Nothing but the numbers

-Glenn
0
 

Author Comment

by:kbay808
ID: 40408977
How do I run it for just cell B7 on sheet1?  Also, does this script go on sheet1 or on a separate module?
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40408994
This code goes in a regular VBA module.

You call it in Excel like a regular function.  Add this to an adjacent cell:
=RegExpReplace(B7,"\D","",TRUE)

If you actually want a number:
=VALUE(RegExpReplace(B7,"\D","",TRUE))

If you want to update/replace the original phone numbers, set up a temporary column with this formula, copy and replace the results with values (Paste Special... Values) and then paste over the original column.

-Glenn
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:kbay808
ID: 40409178
I’m sorry, but I must not have been clear.  I need to be able to paste the phone number into cell B7 and have the result also be in cell B7.  That is why I’m looking for a vba solution.  Otherwise I would of used the substitution function.
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40409401
If you want a VBA solution that isn't a UDF, then here is a subroutine also uses regular expressions to do this, but is specific to column B.
Option Explicit
Sub Fix_Phone_Nos()
    Dim rng As Range
    Dim cl As Object
    Dim RegX As Object
     
    Set RegX = CreateObject("VBScript.RegExp")
    With RegX
        .Pattern = "\D"
        .Global = True
        .IgnoreCase = True
    End With
    
    Set rng = Range("B2:B" & Cells.SpecialCells(xlLastCell).Row)
    For Each cl In rng
        cl.Value = RegX.Replace(cl.Value, "")
    Next cl
End Sub

Open in new window


Regards,
-Glenn
0
 

Author Closing Comment

by:kbay808
ID: 40409771
Thank you very much
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question