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.

**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!

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

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

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

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

2023-06-17 11:41:50

J. Woolley

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

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

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.

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.

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

Copyright © 2024 Sharon Parq Associates, Inc.

## Comments