Adding an Area Code

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.

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.