Friday, October 28, 2016

Excel - Formatting Phone Numbers

How-to article: How to format any phone number, no matter how badly typed, into a standardized format, complete with optional punctuation and optional default area-codes.  For example, (208)1234567 could reformat to 208.123.4567 or 208-123-4567.

This article was written for Excel 2013, but works with any version of Excel.
It uses Macros - don't panic - this is easy

In an example spreadsheet, a simple formula can strip all punctuation from a phone number, leaving only digits, or it can re-format the number into a preferred style.  The formula can be copied down a large list of numbers. 

Five common styles are supported; this illustrates three of them:

Click for larger view

For example, consider these badly-typed phone numbers:

208-123-4567    (properly punctuated, but you might prefer European periods)
208.123-4567    (mixed punctuation)
(208)123-4567  (parenthesis, no spacing)
208/1234567     (inconsistent punctuation or no punctuation)
   123-4567        (leading spaces, no default area-code)

Other problems might include
  • Missing area-codes
  • International dialing prefixes
  • Phone numbers with extraneous leading "1" or "9" prefixes
  • Extensions

The formula, described below, automatically fixes all punctuation and return a properly-formatted number, in the style you prefer.
  • Can force missing (default) area-codes; converting 7-digit to 10-digits
  • Enforce area-code punctuation with hyphens, periods or parenthesis
  • Punctuate with American-hyphens or European.periods (my favorite)
  • Retains extension information, keying on an " x"
  • Extensions can be numeric or text  (e.g. x789  or x Bob's Cell)
  • Supports international dialing prefixes
  • The formula can be easily moved to other spreadsheets
  • Bonus:  A "StripNonNumeric" function, which can remove all punctuation and letters

How it works

Excel does not have a good way to punctuate phone numbers.  But this article shows how you can invent your own formula  -- "=FormatPhoneNumber"-- which behaves much like any other Excel formula.  The formula can be used in any spreadsheet and is easily transportable. 

The formula takes advantage of Visual Basic and you will be writing a "User Defined Function" - also known as a Macro.  Although this is an advanced topic, the instructions here will guide you through the steps. Novice Excel users can accomplish this.

Because this is a Visual Basic routine, you can change the logic if you don't like how it behaves.  Once you learn this technique, you can write your own routines.

Macro Design

The macro is composed of two major verbs.  As you will see, I like to number them:

A700_FormatPhoneNumber( )
A790_StripNonNumerics ( )

The FormatPhoneNumber routine is a mini-program that takes a cell, removes all punctuation, then re-assembles the phone-number using your preferred formatting style.  While it is doing this, it pays attention to the area-code, dialing prefixes, and extensions. The styles are controlled with on-the-fly parameters.

The A790_StripNonNumerics is a stand-alone function that is used by the formatting logic.  It removes all numeric values from a cell, including internal spaces, punctuation, letters and other characters, leaving only the digits.  This can be used for non-phone-number strings.

The Formula

Imagine cell A1 contains "208/123-45 67"  and you want it re-formatted as "208.123.4567", placing the results in the next cell, B1.  The formula, which begins with an equal-sign, looks like this: 

This formula will not work until the macro-code that supports it is linked into the sheet.  I have made this easy by writing the code and you are welcome to download.  The download has no strings attached; it is free, with no registration or advertisements. 

Steal My Code

Read the rest of this article if you want to know how to write your own function.  This can get technical.  Or you can download my spreadsheet and try this yourself.  You are invited to download the Excel spreadsheet and Macro from Keyliner's public GDrive - 2 files:

Download Steps:

1.  Prep:  Using Windows File Explorer, create a new folder to hold the optional, but recommended Macro file.  Use any location desired.  I recommend the following:

Create folder C:\Data
Create sub-folder:  C:\Data\Source
Create sub-folder: C:\Data\Source\CommonVB

2.  From the link below, open the keyliner GDrive folder.

Download the spreadsheet, (which contains both sample data, formulas and a fully-tested and ready-to-use macro).  Note this is an "xlsm" (an Excel Macro Sheet).  Save to any location, such as C:\Data\Wksheets or "Documents"

Keyliner Excel Phone Number Formatting Download

Two files are in the folder.  "Other-mouse-click" and download to your local workstation.

3.  Optionally download the Macro (A700_Format.bas) as a separate (second) file and save to the recommended CommonVB folder, created in Step 1.  The Macro is needed if you want to use these techniques in your own spreadsheets.  I recommend downloading this file.

Note: This is a ".bas" Visual Basic Excel Macro. 

Quick Testing:

A.  On your workstation, launch Excel.

B.  Open the downloaded sheet, "C:\Data\Wksheets\PhoneNumberMacros.xlsm"
      If prompted to enable macro, approve.

      You may need to enable Macros.

      -Select File, Options, Trust Center, Trust Center Settings
      -In the Macro Settings, consider enabling all Macros
       Or add a trusted location, where you saved this particular sheet.

       Admittedly, Microsoft is nervous, especially if you download macros
       from the Internet.  You should be cautious of content you do not trust.
      If you do not trust this content, review the code at the end of this article
      and write your own version of this macro.

C.  In the sample sheet, in the first tan-colored, cell A12,
      type a test phone number of your choosing and press Enter. 

     All example phone numbers in that row re-calculate and re-format, using various options.

D. If curious, view the code and change the Default AreaCode:
     Anywhere within the sheet, Press Alt-F11.  This opens the Visual Basic Editor
     On the Left-Nav, expand Modules
     Double-click A700_Format
     Review code

     Press ctrl-F (find).  Search for "*Change Default AreaCode*" (no quotes)
     Change "208" to your local AreaCode (quotes required)    

 E.  Once your default AreaCode has been set,

      On the left-nav tree, "other-mouse-click" A700_Format
      Chose "Export File"
      Tunnel to "C:\Data\Source\CommonVB"
      Export the file as "A700_Format.bas", overwriting your previous download

      You have just changed the macro.
       Click "X" and close the Visual Basic Editor

       In cell A12, Type a new test phone number, without an AreaCode (e.g. 8884567).
       Press Enter.  Notice the new default areacode is applied.

F.  Close and save the example sheet, which preserves the new default areacode.
     Note the sheet must be saved as an .xlsm (macro sheet). 
     (File, Save-As; change the type to a Macro sheet)

Linking A700_Format to any sheet

To use the new formatting formula in your own sheet, follow these steps.

1.  From any new or existing spreadsheet, press Alt-F11. 
     This opens Visual Basic.

2.  On the left-nav, locate the spreadsheet's name.  You may have more than one spreadsheet open and all will be reflected in the list.  Locate by the worksheet's name.  In this example, see "Book1".  (To make this example simpler, it is best to close all other spreadsheets.)

2.  "Other-mouse-click" the sheet's name.  Choose "Import Files"
     Browse to the location where A700_Format.bas was saved

     For example:  C:\Data\Source\CommonVB\A700_Format.bas

3.  Close the Visual Basic Editor ("X") and return to your worksheet.
     The macro is linked and is ready-to-use.  No need to compile.

4.  In any cell, say A1, type an un-formatted phone number:

     For example: "8884567"  (phone number, no areal-code)

5.  In cell B1, type this formula and press Enter

     =A700_FormatPhoneNumber(A1,"-",true, false)

     A1 = the "bad" phone number
     "-" = the punctuation you would like to use.  I prefer "." (periods) instead of dashes
     true = force default areacode when missing.  This is the word "true"
     false = use parenthesis around the areacode (ugly and unnecessary clutter)

     All four parameters are required.  Use commas to separate.  Parenthesis and quotes required.

Results: Note the phone number is properly punctuated, and a default AreaCode was applied.

If your default AreaCode is incorrect, see "Quick Testing", above.  Those steps change the .bas file.  Then re-export, and re-import into a new spreadsheet to confirm.

Features of Interest

  • Missing area-codes can optionally be populated with a default area-code.  See boolforceAreaCode = true - which is the first "true/false" in the formula in step 5.  See the steps above for how to change the default AreaCode

    AreaCode punctuation (parenthesis) is controlled with the second "true/false".  Use false to disable area-code parenthesis.
  • If a phone number contains a trailing " x" (lower-cased space-x, as in "extension), all text afterwards is preserved.  "208.888.4567 x107"    or   "208.888.4567 x Office"
  • If international dialing, use a "+" prefix, as in "44+208.888.4567.  I do not have a lot of experience with international dialing.  I welcome examples and suggestions on this.
  • If this is a 7-digit or 10-digit number that begins with a "1" or a "9", this routine removes those prefixes.  For example, 1-208-888-4567  becomes 208-888-4567.
  • If the phone number is short,  e.g. "4567", it is not formatted and is assumed to be a local extension.  Similarly, if the number is too long or not recognized as a phone number, it returns, as-typed, without modification.
  • If the phone number begins with an asterisk (*), the number is left exactly-as-typed, with no formatting.
  • A sister formula, "A790_StripNonNumerics" can remove all non-numeric values from any cell, leaving only digits.  "(208) 888-4567" becomes "2088884567" and a part number "ABX5000-102" becomes "5000102".


If you are skilled in Visual Basic Macros, below is the source code.  Copy and paste into any new module.  This is good, tight code, with nothing sneaky.  I welcome suggestions and enhancements.

Option Explicit

Public Function A700_FormatPhoneNumber(ByVal strpassedCell As String, strpunctuation As String, boolforceAreaCode As Boolean, boolrequireParens As Boolean) As String
  'Returns a formatted phone number, regardless of how badly-typed

  'VBA Macro: Tested in Excel 2013
  'Should work in all versions of Excel, old and new

  'Macro by Tim Wolf (c)2016
  'Article describing:

  'Macro is free to use in all private and commercial needs

  'Required: In this script, search for "*Change Default AreaCode*" and change to your preference

  'Use: =A700_FormatPhoneNumber("(208-123.4567 x107 Bob Smith's Phone",".",true,false)
  '   Returns string: 208.123.4567 x107 Bob Smith's Phone"
  '   "123-4567" returns "208.123.4567"

  'where: strpunctuation is typically a hyphen "-" or a "."
  '    boolForceAreaCode - adds default area-code, if missing.
  '              Area-code (here as "208") is hard-coded in the macro; change as needed
  '              Use word "true" or "false"
  '    boolrequireParens - Adds parenthesis to all area-codes e.g. "(208) 123-4567", "(703) 123-4567"
  '              Use word "true" or "false"

  Dim strdefaultAreaCode As String
  Dim strfrontside As String
  Dim strbackside As String
  Dim strformatted As String

  Dim boolinternationalDetected As Boolean
  Dim boolextensionDetected As Boolean
  Dim strinternationalPrefix As String
  Dim strextension As String

  Dim ihpos As Integer

  boolinternationalDetected = False
  boolextensionDetected = False

  'Early-exit: If the phone number begins with an asterisk - leave-as-typed, unmodified
  If Left(Trim(strpassedCell), 1) = "*" Then
   strpassedCell = Trim(strpassedCell)
   'Lob-off the asterisk
   A700_FormatPhoneNumber = Mid(strpassedCell, 2, 999)
   Exit Function
  End If

  '**Change Default AreaCode** Change from "208" to "your preference" (quotes required)
  strdefaultAreaCode = "208"

  If boolrequireParens = True And boolforceAreaCode = True Then
   strdefaultAreaCode = "(" & strdefaultAreaCode & ")"
  End If

  'Look for international prefixes (e.g. 44+455-123-4567, where a plus is required)
  strfrontside = Left(strpassedCell, 4)
  ihpos = InStr(1, strfrontside, "+")
  If ihpos > 0 Then
   'Siphon-off the prefix
   boolinternationalDetected = True
   strinternationalPrefix = Trim(Left(strpassedCell, ihpos - 1))

   'Reset passed cell, truncating the international prefix
   strpassedCell = Mid(strpassedCell, ihpos + 1, 999)

  End If

  ihpos = InStr(7, LCase(strpassedCell), " x")  //Look for space-x extensions
  If ihpos > 0 Then
   'Siphon-off the extension, as in 123-4567 x123
   boolextensionDetected = True
   strextension = Trim(Mid(strpassedCell, ihpos + 1))

   'Reset passed cell, truncating the extension
   strpassedCell = Trim(Left(strpassedCell, ihpos - 1))

  End If

  strpassedCell = A790_StripNonNumerics(strpassedCell)  'remove all other characters

  Select Case Len(strpassedCell)
  Case 0 To 6
   'Return the phone number unformatted, with no changes
   strformatted = strpassedCell

  Case 7
   'Standard, non-areacode number 3-4
   strformatted = A701_Format7Digits _
          (strpassedCell, _
          strpunctuation, _
          strdefaultAreaCode, _
          boolforceAreaCode, _

  Case 8
   'Non-standard phone number; check for "1" or "9" prefix
   'discard 1+ (long-distance) or 9+ (outside-line) prefixes
   'e.g. 9123-4567
   '   9-123-4567 (the dash was pre-stripped)

   If Mid(strpassedCell, 1, 1) = "1" Or Mid(strpassedCell, 1, 1) = "9" Then
     'discard prefix and treat as a standard 7-digit phone number
     strpassedCell = Mid(strpassedCell, 2, 99)
     strformatted = A701_Format7Digits _
            (strpassedCell, _
            strpunctuation, _
            strdefaultAreaCode, _
            boolforceAreaCode, _
     'Unsure what to do with this number; return unmodified
     strformatted = strpassedCell
   End If

  Case 9
     'Unsure what to do with this number; illogical; Return unmodified
     strformatted = strpassedCell

  Case 10
     'Standard 10-digit phone number (with area-code): '2081234567 (e.g.208-123-4567)
     strformatted = A702_Format10Digits(strpassedCell, strpunctuation, boolrequireParens)

  Case 11
     'Possible 10-digit phone number with a 1+ or 9+ prefix
     '12081234567 (e.g. 1-208-123-4567)

     If Mid(strpassedCell, 1, 1) = "1" Or Mid(strpassedCell, 1, 1) = "9" Then
      'discard prefix and treat as a standard 7-digit phone number
      strpassedCell = Mid(strpassedCell, 2, 99)

      strformatted = A702_Format10Digits(strpassedCell, strpunctuation, boolrequireParens)
      'return unformatted
      strformatted = strpassedCell
     End If

  Case 12
    'Possible international number; this is not supported; international numbers should use a "+"
    'and the prefix would have been stripped earlier; attempt anyway
    '442081234567  (e.g. 44+208-123-4567)

    If boolinternationalDetected = False Then
      'Could not already have an internation prefix
      'Since this is 12 long, assume the first two digits are the international prefix
      boolinternationalDetected = True
      strinternationalPrefix = Left(strpassedCell, 2)
      strpassedCell = Mid(strpassedCell, 3, 99)
      strformatted = A702_Format10Digits(strpassedCell, strpunctuation, boolrequireParens)
      'An international prefix was already detected; return unmodified
      strformatted = strpassedCell
    End If

  Case Else
    'return unformatted
    strformatted = strpassedCell

  End Select

  'All functions fall through here: Re-assemble

  If boolinternationalDetected = True Then
   strformatted = strinternationalPrefix & "+" & strformatted
  End If

  If boolextensionDetected = True Then
   strformatted = strformatted & " x" & strextension 'exactly as-typed
  End If

  A700_FormatPhoneNumber = strformatted

End Function

Private Function A702_Format10Digits(strpassedValue As String, strpunctuation As String, boolrequireParens As Boolean) As String


  Dim strareaCode As String
  Dim strfrontside As String
  Dim strbackside As String
  Dim strformatted As String

  strareaCode = Left(strpassedValue, 3)
  strfrontside = Mid(strpassedValue, 4, 3)
  strbackside = Mid(strpassedValue, 7, 4)

  If boolrequireParens = True Then
    strareaCode = "(" & strareaCode & ")"
    strformatted = strareaCode & " " & _
           strfrontside & strpunctuation & _
    strformatted = strareaCode & strpunctuation & _
           strfrontside & strpunctuation & _
  End If

  A702_Format10Digits = strformatted

End Function

Private Function A701_Format7Digits(strpassedValue As String, strpunctuation As String, strdefaultAreaCode As String, boolforceAreaCode As Boolean, boolrequireParens As Boolean) As String

  Dim strfrontside As String
  Dim strbackside As String
  Dim strformatted As String

   strfrontside = Mid(strpassedValue, 1, 3)
   strbackside = Mid(strpassedValue, 4, 4)

   If boolforceAreaCode = True Then
     If boolrequireParens = True Then
      'Append areacode plus a cosmetic space: (208) 123.4567
      strformatted = strdefaultAreaCode & " " & _
              strfrontside & strpunctuation & _
      'Append areacode with normal punctuation, no spaces: 208.123.4567
      strformatted = strdefaultAreaCode & strpunctuation & _
              strfrontside & strpunctuation & _
     End If
     'No areacode is required
     strformatted = strfrontside & strpunctuation & _
    End If

   A701_Format7Digits = strformatted

End Function

Public Function A790_StripNonNumerics(ByVal strpassedCell As String) As String

  'Removes all non-numeric values from a string and returns a string of digits
  'Example: =A790_StripNonNumerics("(208)-123.4567 Bob")  Result: "2081234567"

  'VBA Macro: Tested in Excel 2013; will work in all versions of Excel, old and new
  'Macro by Tim Wolf (c)2016
  'Article describing:

  'Macro is free to use in all private and commercial needs

  'Trim all spaces and non-numerics, including decimals
  'This is not as sophisticated as the C# version

  Dim strstripped As String
  Dim i As Integer

  For i = 1 To Len(strpassedCell) Step 1
    Dim strcurrentChar As String
    strcurrentChar = Mid(strpassedCell, i, 1)
    If (IsNumeric(strcurrentChar)) Then
     strstripped = strstripped & strcurrentChar
     'skip this character
    End If
  Next i

  A790_StripNonNumerics = strstripped

End Function

Related Keyliner Articles:
Excel: How to write UDF Functions
Excel: Parsing City-State-Zip
Excel: Parsing First/Last Names
Excel: SuperTrim and other cool functions

1 comment:

  1. This is huge timesaver! This saves hours of manual clean up of phone numbers prior to loading into another app for calling out.

    I love how it is easy enough to understand and share with non-technical staff. This is a very repeatable process.

    Great job!


Comments are moderated and published upon review.