require 9 digit input no duplicates

[Alphazulu]

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

 

[texasalynn]

=and(len($b3)=9,countif($b$3:$b$238,b8)=1)

 

[Alphazulu]

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]

I think it should be

=Len($B3)=9*COUNTIF($B$3:$B$238,B3)=1)

lenze

 

[texasalynn]

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]

That worked. Thank you!