Thursday, October 20, 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.

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. 

Illustrated are 3 different styles.  Five basic style-combinations are supported:

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, assumed area-codes
  • International dialing prefixes
  • Phone numbers with leading "1" or "9" prefixes
  • Extensions

The formula, described below, can automatically fix all punctuation problems 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 boring hyphens or European periods - my preference
  • Retains "extension information"
  • Extensions can be numeric or text  (e.g. x789  or xBob'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


Excel does not punctuate phone numbers and there is no out-of-the-box formula.  But this article shows how you can invent your own formula  -- "=FormatPhoneNumber"-- which behaves much like an =Sum.  Use the formula in multiple spreadsheets, anytime needed.

The formula takes advantage of Visual Basic and you will be writing a "User Defined Function" (UDF).  Although this is an advanced topic, the instructions here will guide you through the steps. 

Since this is a Visual Basic routine, if you don't like how it behaves, you can change the logic to suit your needs.

Macro Design

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

A700_FormatPhoneNumber( )
A790_StripNonNumerics ( )

The routine 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, any dialing prefixes and extensions. The styles are controlled on-the-fly with parameters.

A790_StripNonNumerics can be used as a stand-alone function to remove 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 would look like this:

You can't type this formula in a spreadsheet until the macro-code that supports it is linked into the sheet.  To make this work, write a 'User Defined Function'.  UDF's are relatively easy, but the actual phone number logic is complicated for a non-programmer.  Fortunately, I made this easy by writing the code and you are welcome to 'borrow it' and use it for your projects.  No strings.  No charges. No registration. 

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

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

Link:  Keyliner Excel Phone Number Formatting Download

3.  Download the Macro as a separate (second) file and save to the recommended CommonVB folder, created in Step 1.  You will need this macro if you want to use these techniques in your own spreadsheets.  It is recommended you download and save this file.

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

Quick Testing:

A.  On your workstation, launch Excel.

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

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

C.  In the first colored cell, A12, type a phone number, press Enter. 
     All formulas in that row will re-calculate and re-format the number.

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

F.  Close the example sheet.  Again, note this must be saved as an .xlsm (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"

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 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: "1234567"

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 "." instead of dashes
     true = force default areacode when missing
     false = use parenthesis around the areacode (ugly and unnecessary clutter)

     All four parameters are 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.  Re-import into a new spreadsheet to confirm.

Features of Interest

  • Pre-typed AreaCodes (e.g. "712") are not replaced with a default AreaCode; the typed AreaCode is maintained.  But if an AreaCode is missing, and boolforceAreaCode = true, a default AreaCode is pre-pended.  See the steps above for how to change the default AreaCode.
  • If a phone number contains a trailing "x" (lower-case-x, as in "extension), all text afterwards is preserved.  208.123.4567 x107    or   208.123.4567 xOffice
  • If international dialing, use a "+" prefix, as in "44+208.123.4567.  I do not have a lot of experience with international dialing.  I welcome improvements.
  • 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-123-4567  becomes 208-123-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 is 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) 123-4567" becomes "2081234567" 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.  Nothing sneaky here, but good, tight code.  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")
  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