Data validation in a cell

[Paul Bergholtz]

Hi all,
I need to validate the value in a cell. The cell is a part of a sheet, which have high priority. I'm thinking of doing this as a Custon Data Validation. But I can't make it work...
* The cell is numeric (used as an input for a calculation)
* The input length of the value must be like this: XXXXXXX,XX. If not, it should gererate an Error Alert.
* The digit 0 counts as a valid number
Any help would be most appreciated.

Regards,
//Paul

 

[NARAYANK991]

Hi Paul ,

See if this is correct :

=AND(ISNUMBER(A1),OR(A1=0,AND(A1>=1000000,A1<10000000,MOD(A1,1)<>0)))

where the DV is for cell A1.

Narayan

 

[Khalid NGO]

Hi Paul,
Not sure about your requirement...

Can you check this in data validation / custom:
=OR(AND(LEN(A1)=9,ISNUMBER(A1)),A1=0)

Regards,

 

[Paul Bergholtz]

Hi,
Narayan: Sorry, but it doesn't work.
Khalid: When you're about to enter a numeric value in (cell F9), you have to write it like this: 1234567,89. That is 7 integers, a comma and 2 decimal numerals.
If you write 12345,67 you 'll get an Error Alert.
Hope this helps, and thank you for taking your time to help me.
//Paul

 

[Khalid NGO]

Ok can you check this:
=OR(AND(LEN(A1)=10,IFERROR(FIND(",",A1)=8,0)),A1=0)

 

[Paul Bergholtz]

Khalid,
Your code solved part of my problem, but it put me on the right track so I managed to solved it.
Thank you for your input.
Regards,
//Paul

 

[Khalid NGO]

I was afraid about the output as text value (not numeric).
Glad you solved it Paul,
Thanks for the feedback.

Regards,