Data Validation - "A" and 2 number and 2 letters

[clarksonneo]

Hi,

I would like to create a Data Validation.

I hope that, in a column, the text in each cell has the following form:

A number 1 number 2 letter 1 letter 2
Eg,

A11AA
A34ER
A76KL
A09RR

The first character must be "A"
The 2nd and 3rd character must be numbers.
The 4th and 5th character must be letters.

How can I create such Data Validation?

Thanks

[nepotist]

Use the Custom Validation and in the formula use the following


=AND(LEFT(B4,1)="A",OR(ISEVEN(MID(B4,2,2)),ISODD(MID(B4,2,2))),ISTEXT(RIGHT(B4,2)))

I assume you have only 5 characters in the validation cell. Change the Cell "B4" according to your needs

[shrivallabha]

Go to Data >> Validation >> Custom and then place a formula for cell A1 as:

=AND(NOT(ISNUMBER(ABS(LEFT(A1,1)))),ISNUMBER(ABS(MID(A1,2,2))),NOT(ISNUMBER (ABS(MID(A1,4,1)))),NOT(ISNUMBER(ABS(RIGHT(A1,1)))),LEN(A1)=5)
Then copy it and do paste special (Validation) on all cells where you need it. There could be a lot better and elegant solution to it.

[RonMcK]

Clarksonneo,

Then, copy this cell down for the length of the column that you want to validate. On the Custom Validation page, you can also enter a message that displays when your user hovers the cursor over any of the cells in the column, reminding them of how the entry is to be formed. And you can define an error message for when they goof up.

Cheers!