How to validate if a given mobile num has 10 digits?

[Paulo]

Hi,
I need to recognise if a given mobile number in a cell has 10 digits.
I can manually filter for less than or more than 10 digit numbers and add a comment.
However, keen to know if there is a smarter way of using this in excel.

Pls help. I have attached a file with the scenario.

 

[NARAYANK991]

Hi Paulo ,

There are 2 ways to check this :

1. =IF(LEN(B4)=10,"Correct","Wrong")

This is checking to see whether the data in cell B4 is 10 characters long. It does not check whether the characters are numeric.

2. =IF(AND(B4>=9000000000,B4<=9999999999),"Correct","Wrong")

This checks whether the number is between two values ; if any of the characters are non-numeric , the number will not pass the test. Of course , it is possible that not all the numbers between the two limits are valid phone numbers , but then the test will become more complicated.

Narayan

 

[bobhc]

Good day Paulo

This will count the numbers but ignore any decimal, so 12345 = 5 and 12.345 = 5

=IF(INT(A1)=A1,LEN(A1),LEN(A1)-1)

 

[Abhijeet R. Joshi]

@Bobhc,Narayan,Paulo:

Should this help?

=IF($A1="","",IF(ISNUMBER($A1),IF(LEN($A1)<>10,"Invalid #","Valid #"),"Incorrect Format"))

Just in case Paulo is sure the values will not have any decimals..

Hope this helps...

 

[NARAYANK991]

Hi Abhijeet ,

Sure. The ISNUMBER function will return FALSE if the data contains non-numeric characters.

Narayan

 

[Paulo]

@Narayan, bobhc, Abhijeet - Awesome functions!
I've never used such combination function for IF.

=IF($A1="","",IF(ISNUMBER($A1),IF(LEN($A1)<>10,"Invalid #","Valid #"),"Incorrect Format")) works out well for me as ISNUMBER gives me additional assurance with the result.

Thank you guys!