How to force consistent phone number format in Microsoft Excel

Combine an Excel custom format with data validation and take the pressure of perfecting inputs off the workforce by letting Excel do the work.

Validate Microsoft Excel data is essential to prevent erroneous information. For example, a phone number that isn’t really a phone number is of no use to anyone. This can happen when someone enters alpha characters, or they can enter too few or too many numeric characters. They won’t do this intentionally, but mistakes happen. To make sure the phone number is a valid phone number, you can use a custom number format, but that’s not enough.

In this tutorial, I’ll show you how to combine custom number format and data validation to force users to enter the correct number of numeric digits. Unfortunately, nothing can prevent the user from entering the wrong numbers.

TO SEE: Windows, Linux, and Mac Commands Everyone Should Know (Free PDF) (TechRepublic)

I use Microsoft 365 on a Windows 10 64-bit system, but you can use earlier versions of Excel. Excel for the web supports custom formats and data validation.

Determining requirements in Excel

Phone numbers have 10 digits and generally use the format (xxx) xxx-xxxx. Let’s break down our phone number requirements:

• Must be 10 characters
• Must display the (xxx) xxx-xxxx format
• Must be numeric digits

The first thing that jumps out is the (xxx) xxx-xxxx format. A custom format can take care of that. The other two require data validation because a custom format will not reject an inappropriate input – it will accept the input but not apply the format.

There are two custom formats that help format phone numbers:

• (###) ###-#### — the # character is a placeholder for numbers
• (000) 000-0000 — The 0 character is also a placeholder for digits, but displays a 0 if there aren’t enough digits entered.

Image A shows both custom formats applied to the same phone number. What you quickly notice is that neither of these custom sizes solves every problem. As long as the input value is 10 numeric digits, both formats work as expected. If not, though, neither format will get the job done:

• Neither format works if the input value contains a non-numeric character.
• Neither format works as expected if the input value is less than 10 digits.
• Neither format works as expected if the input value is more than 10 digits.

Image A

The bottom line is that you need both a custom format and data validation.

Apply a custom size in Excel

The solution requires a combination of custom format and data validation. To illustrate, let’s enter the same input values ​​into a table object and see how a custom format and data validation handles each of these values.

First, let’s create the custom layout for column C of the table object displayed in Figure B as follows:

1. Right-click on C3 and choose Format Cells from the resulting submenu.
2. In the Category list, click Custom at the bottom of the list.
3. In the Type control, select General, enter (###) ###-#### and press Enter. If that custom layout is already in the list, select it. There is a space between the characters ) and #.
4. Click OK.

Figure B

C3 is empty, so Excel can’t do anything yet, but we know that if the input value is 10 numeric digits, the format will display the value as expected.

The next step is to add data validation.

Apply data validation in Excel

Excel’s data validation feature allows you to restrict data entry so that your data is accurate. Most data validation checks comply with business rules. For example, you may want to limit the input to only dates or numeric values. Those are simple examples, but some requirements are much more complex and data validation is up to the task. In this case we use an expression.

Now let’s enter a data validation rule to fix the other possible errors:

1. Select C3 if necessary.
2. Click on the Data tab
3. In the Data Tools group, click Data Validation.
4. In the resulting dialog box, choose Custom from the Allow drop-down list.
5. In the Formula control, enter =AND(ISNUMBER(C3),LEN(C3)=10 (Figure C).
6. Click OK.

Figure C

The expression =AND(ISNUMBER(A2),LEN(A2)=10 checks for two conditions:

• Is the entry a number?
• Is the number exactly 10 characters?

If either condition is no, data validation rejects the input.

Now let’s see how the two functions work together to force the correct formatting of phone numbers. Enter 1234567890 in C3 and press Tab. The custom format takes care of this value, as you can see in Figure D.

Figure D

Digits E shows the results of entering a234567890. Because the input value contains a non-numeric character, e, data validation returns an error. Click Redo, replace a with 1, and press Tab. Data Validation accepts the input. Remember that because we are using an Excel table object, Excel will copy both the custom format and the data validation to new records.

Digits E

If you enter 12345678 and 12345678901, the data validation also fails. The former has too few numbers and the latter has too many. Make the necessary corrections so that data validation accepts both, as shown in Figure F.

Figure F

Nothing can prevent staff from entering the wrong numeric character. However, combining these two functions will help other input errors. Many users do not know that you can combine these two functions for better input control.