Mobile numbers are always numeric and of a certain length. You want to ensure data entered is exactly as per that.
In India, mobile numbers are 10 digits. Meaning if any mobile number that’s entered isn’t exactly 10 digits, it’s not a correct data entry.
Similarly, in other countries, mobile number length’s vary. Whether you’re in India, USA, UK Australia, wherever, this method will be extremely useful for you.
What we want
Thus, we want be build a data validation that suffices 2 conditions: –
- Number entered must be Numeric
- Length of the number must be exactly 10 digits (for Indian mobile number)
Which 3 functions will be used
this function will ensure that the number entered in only numeric, no text, no dates
this function will ensure 10 digits only
to ensure both the conditions are met together in ANDing
To know how AND function works, click and read this blog post
Steps to create the custom data validation using these 3 functions
- Create a column for Mobile numbers (say F column)
- Select all the cells (except the header)
- Under Data tab -> Data Validation -> Allow -> Select ‘Custom’
- Enter the formula
- Your Data Validation is done and it will only allow 10 digit – numeric data entry
Logic behind how it works
- The F2 is simply because we are using the F column for the mobile numbers column, 2 is the row from where the data entry starts.
- $F is, we are asking Excel to check only the F column for the 2 conditions.
this checks whether the data entered is numeric or not. Internally, it gives a boolean value as TRUE or FALSE. So if the data entered in numeric, it will be internally TRUE, else FALSE.
this checks whether the data entered in 10 digits or not. Internally, this too gives a boolean value as TRUE or FALSE. So if the data entered in 10 digits, it will be internally TRUE, else FALSE.
ANDing means, both conditions should be TRUE. So only when both the boolean results are TRUE, the data validation will allow the data entered by the user. If any one value is FALSE, the data validation will reject the data entered by the user.