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.
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
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.
The macro is composed of two major routines. As you will see, I like to number them:
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.
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:
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.
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
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
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 '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") 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
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