Data validation

[VARGEESH]

Hi,


Scenario 1:

When entering data in a cell it should start with "0" and should allow upto "099". if else display an error message.


Scenario 2:

While entering data in a cell it should start with "0" and should accept upto 5 digits alone. if else display an error message.


Scenario 3:

A cell should contain upto 6 digits alone. if else display an error message.


Thanks you for your help in advance.


Regards,

Vargeesh

 

[Luke M]

All of these assume that the cell is formatted as text, since you're requiring leading zeros. Data validation automatically display an error message, but you can customize what is displayed. Custom formula for each scenario is as follows.


Scenario 1:

=AND(LEFT(A2,1)="0",LEN(A2)<=3)


Scenario 2:

=AND(LEFT(A2,1)="0",LEN(A2)<=5)


Scenario 3:

=LEN(A2)<=6

 

[VARGEESH]

Hi Luke,


Thanks for your kind advice.


The formula is working for the cell A2 alone.


But i don't know how to do it for the entire column in the "Data Validation" dialog box.


Regards,

Vargeesh

 

[Luke M]

Select the entire column (with cell A2 being the active cell) before applying the data validation. Or, apply validation to only A2, and copy to the other cells.

 

[VARGEESH]

Luke,


Scenario 2 is working.


Rest of the two aren't working.


It is accepting "0" alone.


if i enter any other digit it is throwing the error message.


Eg: 099, 025, 055, etc.


Regards,

Vargeesh

 

[VARGEESH]

Hi,


Sorry. One correction in the above comment.


Scenario 3 is working.


Scenario 1 and 2 is not working.


Thanks,

Vargeesh

 

[XLinXL]

Hi Virgeesh,


The only reason why Luke's solution won't work is you do not have the cells/column formatted as text, as Luke has indicated, since you need to have leading zeros!


Hope this helps!


Regards,