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: –
  1. Number entered must be Numeric
  2. Length of the number must be exactly 10 digits (for Indian mobile number)

Which 3 functions will be used

  1. =ISNUMBER
    this function will ensure that the number entered in only numeric, no text, no dates
  2. =LEN
    this function will ensure 10 digits only
  3. =AND
    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)

    Select column without header

    Select column without header

  • Under Data tab -> Data Validation -> Allow -> Select ‘Custom’
    Data tab

    Data tab

    Custom

    Custom

  • Enter the formula
    =AND(ISNUMBER($F2),LEN($F2))
  • Your Data Validation is done and it will only allow 10 digit – numeric data entry

Logic behind how it works

Formula

Formula

  • 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.
  • =ISNUMBER($F2)
    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.
  • =LEN($F2)=10
    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.
  • =AND
    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.

 

See a step-by-step video