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

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

Inserting Datasheet Rows or Columns

Microsoft Graph provides a handy way to add simple charts to your document without the need for Excel. Those charts are ...

Discover More

Viewing Formula Results

When editing information in a cell, you may need to know the result of a portion of your formula. The shortcut described ...

Discover More

The Navigation pane can be quite handy for quickly jumping to different areas of your document. If the size of the text ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

##### More ExcelTips (ribbon)

Moving the House Number to Its Own Cell

Excel is great at manipulating data, but sometimes it is difficult to figure out the best way to do the manipulation. ...

Discover More

Extracting a Pattern from within Text

If you have a large amount of data in a worksheet and you want to extract information from the text that meets certain ...

Discover More

Returning the Rightmost Value in a Row

Do you need to figure out the rightmost value within a row in which not all cells may contain values? This tip provides a ...

Discover More
##### Subscribe

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

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 4 + 0?

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.