See all How-To Articles

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 numbers final data

 

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.

 

validate phone numbers 1

 

  1. 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).

 

validate phone numbers 2

 

  1. 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.

 

validate phone numbers 3

 

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

 

validate phone numbers final data

 

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…

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.

 

google sheets validate phone numbers

 

  1. 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.

 

google sheets validate phone numbers 1

 

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

 

google sheets validate phone numbers 2