Excel 2019 - data validation

[falcios]

For data validation, I need the columns to be a whole number and no more than six digits. Can you help with this please
Thanks in advance

 

[hajiali]

Try this for the formula for DV. ex is for column A

=AND(LEN($A1)=6,INT($A1)=$A1)

 

[falcios]

=AND(LEN($B1)=6,INT($B1)=$B1)

I'm getting the message, the value doesn't match the data validation restrictions defined for this cell
Retry
Cancel
Any idea what is going ong

 

[hajiali]

That is the Defualt message when the Users input a value that is not 6 digits or whole number. You can customize the message to on the DV menu under "Error Alert" tab

like

Book3
	A
1
2
3
4
5
6
7
8
9
10
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A1:A10	Custom	=AND(LEN($A1)=6,INT($A1)=$A1)

 

[falcios]

Can I use the data validation or do I need the formula as well?

 

[hajiali]

You use the formula in the DV. See Link Here for help

 

[Peter_SSs]

What about these settings?

 

[falcios]

In data validation, I used custom and the above formula, and it's still not working. Do you have any other suggestion to get it to work
Thanks in advance

 

[Peter_SSs]

In data validation, I used custom and the above formula, and it's still not working. Do you have any other suggestion to get it to work
Thanks in advance

Who are you responding to?
My suggestion did not involve any formulas. Excel data validation has a built-in section for exactly what you described that you want to do.

 

[Phuoc]

Another way:

=AND(MOD(B1,1)=0,B1<=999999)