How to Apply Data Validation to Force Phone Number Format in Excel?

Let us assume we have a situation where we want to allow only numbers for a range of cells in Excel and everyone is entering different data other than a phone number. We can make the cells only allow phone numbers. This process has two steps. The first step is to format the cells as numbers, and the second step is to use the concept of data validation to restrict the data to only being a ten-digit number. Read this tutorial to learn how you can apply data validation to force phone number format in Excel in a simple way.

Applying Data Validation to Force Phone Number Format in Excel

Here we will first format the cells, then allow only number formatting using the conditional formatting, and then edit the message. Let's see a simple process to apply data validation to force phone numbers to be formatted in Excel.

Step 1

Consider the possibility of having an Excel sheet with data similar to the data shown in the screenshots below. 

Our first step is to format the cells as numbers.

Select the data where you want to allow only numbers, then right click and select format cells from the menu box. A new pop-up window will be opened as shown in the below image.

Again, in the new pop-up, click on "Custom," then enter the type as "###-###-####," then click "OK" to close the pop-up.

Step 2

Select the data once more, click on Data, and then select Data validation; a new pop-up window will appear.

Select allow to custom in the new pop-up and enter the formula: =AND(ISNUMBER(B2),LEN(B2)=10) in the formula box and press "OK" to finish the process.

Step 3

If you enter any other data other than a ten-digit phone number, then an error message will be displayed, as shown in the below image.

Conclusion

In this tutorial, we used a simple example to demonstrate how we can apply data validation to force phone number format in Excel to highlight a particular set of data.