This article was written for Excel 2013, but works with any version of Excel.
It uses Macros - don't panic - this is easy.
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"
Link: A700_PhoneNumberFormat_Folder
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)
where:
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".
Code
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
'A700_FormatPhoneNumbers
'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: http://keyliner.blogspot.com/2016/10/excel-formatting-phone-numbers.html
'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, _
boolrequireParens)
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, _
boolrequireParens)
Else
'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)
Else
'illogical
'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)
Else
'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
'2081234567
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 & _
strbackside
Else
strformatted = strareaCode & strpunctuation & _
strfrontside & strpunctuation & _
strbackside
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 & _
strbackside
Else
'Append areacode with normal punctuation, no spaces: 208.123.4567
strformatted = strdefaultAreaCode & strpunctuation & _
strfrontside & strpunctuation & _
strbackside
End If
Else
'No areacode is required
strformatted = strfrontside & strpunctuation & _
strbackside
End If
A701_Format7Digits = strformatted
End Function
Public Function A790_StripNonNumerics(ByVal strpassedCell As String) As String
'A790_StripNonNumerics
'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: http://keyliner.blogspot.com/2016/10/excel-formatting-phone-numbers.html
'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
Else
'skip this character
End If
Next i
A790_StripNonNumerics = strstripped
End Function
This article: Link:
A700_PhoneNumberFormat_Folder
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
This is huge timesaver! This saves hours of manual clean up of phone numbers prior to loading into another app for calling out.
ReplyDeleteI love how it is easy enough to understand and share with non-technical staff. This is a very repeatable process.
Great job!