Excel data validation

[Nitesh Khot]

Hi..

I want to restrict data entry from user where User enter mobile no then it should be start with either "7" or "8" or "9" and length of mobile no is 10 digit with no duplicity in range(A1:A10)..

I have used below formula in data validation it works fine but i am unable to restrict no start from "7" or "8" or "9"

formula is :-

=AND(ISNUMBER(A1),LEN(A1)=10,COUNTIF($A$1:$A$18,A1)<=1)

 

[Deepak]

=AND(ISNUMBER(A1),LEN(A1)=10,COUNTIF($A$1:$A$18,A1)<=1,left(a1)>6)

 

[Nitesh Khot]

Thanks Deepak for prompt response....

But still same is not working ity accepts values start from 1, 2 ,3 ,4,5,6

 

[Deepak]

Left fn trough string type output so check after wrapping the "6"


AND(ISNUMBER(A1),LEN(A1)=10,COUNTIF($A$1:$A$18,A1)<=1,left(a1)>"6")

You may evaluate the same also that which part is going wrong there.

 

[Nitesh Khot]

Thanks deepak,

its working fine as below few changes..

=AND(ISNUMBER(H1),LEN(H1)=10,COUNTIF($H$1:$H$18,H1<=1,LEFT(H1,1)>"6")

 

[Nitesh Khot]

how to evaluate above code for error

 

[Deepak]

It should be..
=AND(ISNUMBER(H1),LEN(H1)=10,COUNTIF($H$1:$H$18,H1)<=1,LEFT(H1,1)>"6")


To evaluate paste the same in a cell ,then type in validation cell as required & check output true/false

 

[Nitesh Khot]

Thanks for such a great Help....Take Care

 

[NARAYANK991]

Hi Nitesh ,

You should be aware that the formula will also pass a decimal number such as :

72345.6789

If you wish to exclude decimal numbers , you will have to include one more check in the formula.

Narayan

 

[Khalid NGO]

Hi Nitesh,

This should work:
=AND(COUNTIF($H$1:$H$18,H1)<=1,$H1>6999999999)

Regards,

Edit: oops, missed the check for NUMBER, revised:
=AND(ISNUMBER($H1),COUNTIF($H$1:$H$18,H1)<=1,$H1>6999999999)

 

[NARAYANK991]

Hi Khalid ,

It is better to retain the check for the length of 10 digits.

Narayan

 

[Khalid NGO]

Hi Narayan Sir,
Thanks,

There must be a reason you've advised to retain the check.
I am interested to know / learn.

Regards,

 

[Deepak]

Khalid NGO

As Narayan pointed!

In your approach numbers with decimal like 888888888.8 will also get accepted as its > 6999999999


This would be a solution!

=AND(ISNUMBER(H1),LEN(H1)=10,COUNTIF($H$1:$H$18,H1)<=1,(--LEFT(H1,1))>6,LEN(INT(H1))=10)

 

[Khalid NGO]

In your approach numbers with decimal like 8888888888.8 will also get accepted as its > 6999999999

Deepak Sir,
Thanks, got your point.

OP needs the length of exact 10 digits, my formula fails when the length is more than 10 digits. [forgot the basic thing, how stupid I am today]