How to force consistent phone number format in Microsoft Excel

Combine an Excel custom format with data validation and remove the pressure for input perfection from personnel by letting Excel do the work.

Validating Microsoft Excel data is vital to prevent erroneous information. For instance, a phone number that’s not really a phone number is of no use to anyone. This can happen when someone enters alpha characters, or they may enter too few or too many numeric characters. They won’t do so intentionally, but mistakes happen. To ensure that the phone number is a valid phone number, you might turn to a custom number format, but it won’t be enough.

In this tutorial, I’ll show you how to combine a 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 numeric digits.

SEE: Windows, Linux, and Mac commands everyone needs to know (free PDF) (TechRepublic)

I’m using 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.

How to determine requirements in Excel

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

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

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

There are two custom formats that will help when formatting phone numbers:

• (###) ###-#### — the # character is a numeric digit placeholder
• (000) 000-0000 — the 0 character is also a digit placeholder but displays a 0 when there aren’t enough input digits.

Figure A shows both custom formats applied to the same phone number. What you quickly discern is that neither custom format solves every problem. As long as the input value is 10 numeric digits, both formats work as expected. However, when that’s not the case, neither format gets 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.

Figure A

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

How to apply a custom format in Excel

The solution requires a combination of a custom format and data validation. To illustrate, we’ll enter the same input values into a Table object and see how a custom format and data validation handle each.

First, let’s create the custom format for column C of the Table object shown in Figure B as follows:

1. Right-click 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 format is already in the list, select it. There’s a space character between the ) and # characters.
4. Click OK.

Figure B

C3 is empty, so there’s nothing for Excel to do yet, but we know that if the input value is 10 numeric digits, that the format will display the value as expected.

The next step is to add data validation.

How to apply data validation in Excel

Excel’s data validation feature allows you to limit data input, ensuring that your data is accurate. Most data validation controls fulfill business rules. For instance, you might want to limit 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’ll use an expression.

Now, let’s enter a data validation rule to take care of the other possible errors:

1. Select C3 if necessary.
2. Click the Data tab
3. In the Data Tools group, click the Data Validation option.
4. In the resulting dialog, choose Custom from the Allow dropdown.
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 entry.

Now, let’s see how the two features work together to force the proper 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

Figure E shows the results of entering a234567890. Because the input value contains a non-numeric character, e, data validation displays an error. Click Retry, replace a with 1, and press Tab. Data validation will accept the entry. Remember, because we’re using an Excel Table object, Excel copies both the custom format and data validation to new records.

Figure E

Entering 12345678 and 12345678901 will also fail data validation. The former has too few digits and the latter has too many. Make the necessary corrections so data validation will accept both, as shown in Figure F.

Figure F

Nothing can prevent personnel from entering the wrong numeric character. However, combining these two features will help other input errors. Many users are unaware that you can combine these two features for better input control.