# require 9 digit input no duplicates

#### Alphazulu

##### Board Regular
I am trying to use data validation to restrict entry into a cell.

I want to make sure the entry is 9 digits and can not be repeated in cells cells B3 through B238.

So far =COUNTIF(\$B\$3:\$B\$238,B8)=1 prevents the duplicate/repeated entry. How can I also require entry to be 9 digits?

Thanks

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

#### texasalynn

##### Well-known Member
=and(len(\$b3)=9,countif(\$b\$3:\$b\$238,b8)=1)

#### Alphazulu

##### Board Regular
Hi -
I entered =and(len(\$b3)=9,countif(\$b\$3:\$b\$238,b8)=1) in cell b3 through data validation and the cell still allows me to enter a number less than 9 digits long. Can you see where I missed something?

I used data->validation->settings->custom->formula and(len(\$b3)=9,countif(\$b\$3:\$b\$238,b8)=1)

Thanks[/I]

#### lenze

##### Legend
I think it should be
Rich (BB code):
``=Len(\$B3)=9*COUNTIF(\$B\$3:\$B\$238,B3)=1)``

lenze

#### texasalynn

##### Well-known Member
when you select the area for the data validation, make sure you are on the first cell. So if you highlight column B then the formula has to be adjusted to this
=Len(\$B1)=9*COUNTIF(\$B\$3:\$B\$238,B1)=1)

the way to double check this, is after you added the data validation; select a single cell and look at the data validation. The formula should show that cell range in the formula

#### Alphazulu

##### Board Regular
That worked. Thank you!

Replies
9
Views
186
Replies
10
Views
505
Replies
7
Views
192
Replies
0
Views
229
Replies
5
Views
378

1,191,191
Messages
5,985,215
Members
439,947
Latest member
fabiannic

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back