How to Validate Phone Number Format in Excel & Google Sheets

This tutorial demonstrates how to validate that phone numbers are in the appropriate format.

Validate Phone Number Format

If you want to allow users to enter only valid number formats in Excel, you can achieve it using Data Validation.

1. Select cells where you want to validate entries (B2:B10), and in the Ribbon, go to Data > Data Validation.

2. In the Data Validation window, in the Settings tab, choose Whole number in the Allow drop-down, and enter a minimum and maximum limits for the range (1000000000–9999999999).

3. Now, create an alert message that will be displayed is a wrong number format is entered.
   (1) Go to the Error Alert tab, and (2) enter a title (Error) and the (3) error message text (Phone number must be numeric with a maximum length of 10 numbers).
   (4) Finally, click OK.

Now, you can enter only numeric values with 10 digits.

As you can see in the picture above, the entry in cell B2 (2009438217) is allowed, since it has the valid number format. However, the value in cell B2 (923) is not allowed, and the error message is displayed.

See also…

• Format Phone Numbers With Dashes
• How to Set a Character Limit
• Email Address Format Validation

Validate Phone Number Format in Google Sheets

The same data validation rule can also be set up in Google Sheets.

1. Select cells where you want to validate entries (B2:B10), and in the Menu, go to Data > Data validation.

2. In the Data validation window, (1) choose Number in the Criteria drop-down, and (2) enter limits for the range (between 1000000000 and 9999999999).
   Now, (3) select Reject input, and (4) check Show validation help text. The text entered here (Enter a number between 1000000000 and 9999999999) will appear when a user enters a wrong number format.
   Finally, (5) press Save.

As a result, the entered error message appears if you enter the wrong data format.