Open In App

Use Custom Data Validation Function To Limit Number of Digits In Excel

Improve
Improve
Like Article
Like
Save
Share
Report

There might arise a situation while working on Excel where you want the users to type in 10-digits(say)  numbers only in a cell. In these types of scenarios, we can make use of the Custom Data validation Function to limit the number of digits in Excel. In this article, we will discuss the same.

Sample Data: Customer Sales Table

Sales Table

Approach:

First, we check the given cell value is number and then the cell value is only 10 digit. Allow only both conditions are true

Excel Formula:

  • ISNUMBER(): Return Boolean value True if the cell value is number.
  • LEN(): Return the number of characters in a cell.
  • AND(): Logical and function.

Implementation of Custom Data Validation Function:

Follow the below steps to implement the custom data validation function:

Step 1: Type Header (“Mobile Number” as an example) in Cell C1.

Step 2: Select cells “C2” to “C21”

Step 3: Click Data >> Data Validation >> Data Validation… to open the Data Validation dialog box as shown below. Here we will be implementing the function as :

AND(LEN(C2)=10,ISNUMBER(C2))

Error Alert:

Data validation popup an Error Alert, when the condition is not met.


Last Updated : 29 Oct, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads