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

When I copy one of the below phone numbers into cell B7 on sheet1, I need it to resolve to (555) 555-5555

555 555 5555
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glenn RayExcel VBA DeveloperCommented:
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

kbay808Author Commented:
How do I run it for just cell B7 on sheet1?  Also, does this script go on sheet1 or on a separate module?
Glenn RayExcel VBA DeveloperCommented:
This code goes in a regular VBA module.

You call it in Excel like a regular function.  Add this to an adjacent cell:

If you actually want a number:

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.

Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

kbay808Author Commented:
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.
Glenn RayExcel VBA DeveloperCommented:
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kbay808Author Commented:
Thank you very much
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.