Adding an Area Code

Written by Allen Wyatt (last updated June 17, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


3

Shannon has a large worksheet that contains phone numbers in one column. A good number of those phone numbers don't include area codes. These are numbers within Shannon's local area code (775). She wonders if there is a quick way to add 775 before these phone numbers without having to type them in manually.

The first thing to figure out is the nature of Shannon's data. The phone numbers could be contained within her worksheet as either text or as numbers. The easiest way to make this determination is to select a cell that contains a phone number and then take a look at what is shown in the Formula Bar. For instance, let's say that you select cell A7, which contains a phone number that looks like this:

555-1212

If you select cell A7 and the Formula Bar shows 5551212 (without the dash), then the cell contains a number that is formatted to look like a phone number. If, however, the Formula Bar shows 555-1212 (the same as what you see in cell A7), then the cell contains text.

What is it important to know if you are dealing with text or with numbers? Because it will affect how you add the area code. Let's say that you determine that your phone numbers are text. In that case, you could enter the following into a blank column:

=RIGHT("775-" & A7, 12)

What you end up with is a properly formatted phone number, with the 775- prefix. If the phone number in cell A7 already contains an area code, then the "775-" prefix is stripped off by the RIGHT function.

If you want a different type of look to your phone numbers, then you can simply modify how the formula adds the prefix, as in the following:

=RIGHT("(775) " & A7, 14)

Again, these approaches work only if your phone numbers are text values. If they are numeric values, then you need to approach them differently. The easiest way would be to examine the length of the numeric value and, if it is less than 8 digits, add the prefix to it:

=IF(LEN(A7)<8,A7+7750000000,A7)

Provided that you format the result of this formula as a phone number, it should look just the way you want, with the proper 775 prefix added.

Finally, you may have the situation where your phone numbers are a mixed lot—some are text and some are numeric. In that instance, it is easiest to use a macro that can examine your source information, determine what type of data it is, and then make the adjustments to the phone numbers. The following macro will take a look at whatever cells you select and convert any phone numbers within those cells to formatted text:

Sub FixPhones1()
    Dim c As Range
    Dim sRaw As String
    Dim sNew As String
    Dim J As Integer

    Const sFmt As String = "###-###-####"

    For Each c In Selection
        ' Don't want to affect formulas
        If Not c.HasFormula Then
            sRaw = Trim(c.Text)
            ' Remove anything not a digit
            sNew = ""
            For J = 1 To Len(sRaw)
                If Mid(sRaw, J, 1) Like "[0-9]" Then sNew = sNew & Mid(sRaw, J, 1)
            Next J
            ' Don't make any changes if there are not exactly 7 or 10 digits
            If (Len(sNew) = 7) Or (Len(sNew) = 10) Then
                ' Add area code, if necessary
                If Len(sNew) = 7 Then sNew = "775" & sNew
                sNew = Format(sNew, sFmt)
                c.Value = sNew
            End If
        End If
    Next c
End Sub

In order to get the formatted output you want, make sure you change the sFmt constant so it reflects the desired format pattern. Then, select the cells you want to change and run the macro. If a selected cell contains a formula or it doesn't contain either 7 or 10 digits, then it is not affected.

If you prefer your phone numbers to end up as numeric values, then your macro needs to be different. Here is an example of one that will work in this situation:

Sub FixPhones2()
    Dim c As Range
    Dim sRaw As String
    Dim sNew As String
    Dim J As Integer

    Const sFmt As String = "[<=9999999]###-####;(###) ###-####"

    For Each c In Selection
        ' Don't want to affect formulas
        If Not c.HasFormula Then
            sRaw = Trim(c.Text)
            ' Remove anything not a digit
            sNew = ""
            For J = 1 To Len(sRaw)
                If Mid(sRaw, J, 1) Like "[0-9]" Then sNew = sNew & Mid(sRaw, J, 1)
            Next J
            ' Don't make any changes if there are not exactly 7 or 10 digits
            If (Len(sNew) = 7) Or (Len(sNew) = 10) Then
                ' Add area code, if necessary
                If Len(sNew) = 7 Then sNew = "775" & sNew
                c.Value = sNew
                c.NumberFormat = sFmt
            End If
        End If
    Next c
End Sub

Note that the primary difference in this macro variation is that whatever is stored in the sFmt constant is assigned to the NumberFormat property for the cell being modified.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13383) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Excellent XML Resource Available

Where to get more information on XML.

Discover More

Making the 'Welcome Back' Message Consistent and Permanent

When you open a document on which you previously worked, Word displays a "Welcome back" message that can help return you ...

Discover More

Inserting Dashes between Letters and Numbers

If you need to add dashes between letters and numbers in a string, the work can quickly get tedious. This tip examines ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Combining Cell Contents

Excel allows you to easily combine text together. Interestingly, it provides two ways you can perform such combinations. ...

Discover More

Listing Combinations

You can easily use the COMBIN worksheet function to determine the number of combinations that can be made from a given ...

Discover More

Only Showing the Maximum of Multiple Iterations

When you recalculate a worksheet, you can determine the maximum of a range of values. Over time, as those values change, ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 3 + 4?

2023-06-17 11:41:50

J. Woolley

The Tip's FixPhones2 macro ignores any formula cells that have a phone number result. Here is an alternative; adjust Const AC, NF, MN, or MX if necessary.

Sub FormatPhoneNumbers()
    Const NS As String = vbNullString, AC As String = "775" 'local area code
    Const NF As String = "[<=9999999](" & AC & ") ###-####;(###) ###-####"
    Const MN As Long = 1000000 'minimum phone number numeric value
    Const MX As Double = 9999999999# 'maximum phone number numeric value
    Dim cell As Range, valu As Variant
    For Each cell In Selection
        valu = cell.Value
        If Not IsNumeric(valu) Then 'remove formatted phone number tokens
            valu = Replace(Replace(Replace(Replace(valu, _
                "(", NS), ")", NS), "-", NS), " ", NS)
        End If
        If IsNumeric(valu) Then
            valu = Val(valu)
            If valu = Int(valu) And valu >= MN And valu <= MX Then
                If Not cell.HasFormula Then cell.Value = valu
                cell.NumberFormat = NF
            End If
        End If
    Next cell
End Sub

Select the column with phone numbers (text or numeric), then run the macro. Text constants that qualify as USA phone numbers are converted to numeric values; formulas are not altered. All qualified numeric values (constants or formula results) are formatted as USA phone numbers with area code, including the local area code if applicable. The format of other cells is not altered.
For similar discussion, see https://excelribbon.tips.net/T011802_Converting_Phone_Numbers.html


2023-06-17 07:56:00

Ron S

I agree with Allen, the first thing to decide is if your Phone Numbers are in Excel Text or Number format.

Since the only valid characters in a phone number are NUMBERS, my personal bias is to format it as a NUMBER. If (in the future) you export the phone number to some other program, say an auto dialer, it won't like the - ( ), so strip them out.

Once you decide TEXT or NUMBER you have to do some "data cleaning" of your existing data, and set something up to ensure that new data entry will maintain the desired formatting. Is there going to be lots of new data over time, or will it be mostly static with only occasional new entries? Will other people be entering the values?

This is Excel, as usual there are more than one way to reach the desired end point. In addition to the macro, you could do some initial data clean up, then apply a CUSTOM NUMBER FORMAT to the CELLS (or COLUMN) so that future numbers are simply typed and Excel adds the appropriate display formatting, without changing the underlying values. Learning custom number formatting is a useful skill. Here are a couple of links to good articles about custom number formatting. This first article is just about using custom number formats in general.
Excel Custom Number Format Guide
https://www.myonlinetraininghub.com/excel-custom-number-format-guide

Since you want to add "775" only sometimes, you want to do "advanced" Custom Number formatting, or "Conditional Custom Number Formatting). This article shows how to do that
Excel Custom Number Format Conditions
https://www.myonlinetraininghub.com/excel-custom-number-format-conditions

Funny the custom number format code is essentially the same as used in the macro, ie [<9999999]"775-"###-####;###-###-####

Macros are the "traditional" way of repeating actions. A newer alternative to macros is "PowerQuery". Rather than typing all of the underlying code, you can use the ribbon and right click context menus to select commands to generate code one line at a time. Advanced users can manually edit the generated commands to make them more generalized or perform more complex variations. You can go back and edit individual steps in a "Query".

When you get new data all you have to do is "refresh" the query to apply the command to the new data.

Power Query is a separate programming language embedded inside of Excel. It actually has more commands than VBA or Excel itself.


2023-06-17 06:12:04

Graham Rice

It is normally good practice to always format phone numbers as text. They are not "true" numbers, you would not want to perform a mathematical operation (such as addition) with them.

Within some countries, such as mine (UK), all phone numbers have a leading zero. If formatted as a number then Excel would automatically strip away the leading zero.
The zero is only removed and replaced by the country code when receiving a call from another country.

A similar "text" format should also be used for ZIP codes or Social Security Numbers, since likewise these are not "true" numbers.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.