# Dubble numbers

#### bullit_nl

##### Active Member
Hi,

I have a sheet where i use validation to prevent myself (and others) to use the same numbers. This works fine.

Now i also wanna make sure that the numbers i type always must contain 8 numbers.

Ex. 90090001 and not 9009001.

I know this also can be done with validation. But is it possible to have it both?

Thanks
This message was edited by bullit_nl on 2002-10-04 05:30
This message was edited by bullit_nl on 2002-10-04 05:31

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).
On 2002-10-04 05:29, bullit_nl wrote:
Hi,

I have a sheet where i use validation to prevent myself (and others) to use the same numbers. This works fine.

Now i also wanna make sure that the numbers i type always must contain 8 numbers.

Ex. 90090001 and not 9009001.

I know this also can be done with validation. But is it possible to have it both?

Thanks
This message was edited by bullit_nl on 2002-10-04 05:30
This message was edited by bullit_nl on 2002-10-04 05:31

Expand the formula that you use...

=AND(...,LEN(Cell)=8)

On 2002-10-04 05:56, Aladin Akyurek wrote:
On 2002-10-04 05:29, bullit_nl wrote:
Hi,

I have a sheet where i use validation to prevent myself (and others) to use the same numbers. This works fine.

Now i also wanna make sure that the numbers i type always must contain 8 numbers.

Ex. 90090001 and not 9009001.

I know this also can be done with validation. But is it possible to have it both?

Thanks
This message was edited by bullit_nl on 2002-10-04 05:30
This message was edited by bullit_nl on 2002-10-04 05:31

Expand the formula that you use...

=AND(...,LEN(Cell)=8)

The formula is use now is:

=AANTAL.ALS(\$B\$3:\$K\$100;E46)=1

Where (how) should i expand the formula?

Thanks

The formula is use now is:

=AANTAL.ALS(\$B\$3:\$K\$100;E46)=1

Where (how) should i expand the formula?

I assume the formula wrt B3 will look as

=AANTAL.ALS(\$B\$3:\$K\$100;B3)=1

If so, select B3:K100, activate Data|Validation and change the formula to:

=EN(AANTAL.ALS(\$B\$3:\$K\$100;B3)=1,LENGTE(B3)=8)

It works perfect.

On 2002-10-04 08:11, bullit_nl wrote:
It works perfect.

Is it also possible with this formula to check for the dubble numbers on more sheets in one workbook?

Replies
3
Views
48
Replies
5
Views
360
Replies
2
Views
90
Replies
1
Views
113
Replies
2
Views
287

1,203,514
Messages
6,055,836
Members
444,828
Latest member
StaffordStag

### 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