custom data validation for alphanumeric entry

**wotadude** · 05-18-2009, 05:20 PM

I require a custom formula for in cell data validation of an 5 digit alphanumeric entry.
The valid format is ANNNN (1 x alpha & 4 x numeric).
Case of the aplha is not an issue.
Any help would be appreciated.

**shg** · 05-18-2009, 05:32 PM

The easiest way to develop a validation formula is to put candidate values in a column (say A), and a formula in col B that returns True if the corresponding entry is valid.

In the case, you need a formula with AND, LEN, LEFT, and RIGHT functions.

Then copy the final formula to data validation with references adjusted appropriately.

**teylyn** · 05-18-2009, 05:46 PM

Greetings from the 'Naki.

To validate A1 use custom validation like

=AND(LEN(A1)=5,ISTEXT(LEFT(A1,1)),ISNUMBER(VALUE(RIGHT(A1,4))))

HTH

**wotadude** · 05-19-2009, 04:23 PM

thanks but....
Teylyn - that was similar to what i tried too.
However it still returns true if the first alpha character is substitued for a number.
But with a bit more creativity i came up with -
=AND(LEN(A1)=5,ISERROR(VALUE(LEFT(A1,1))),ISNUMBER(VALUE(RIGHT(A1,4))))
Not pretty but seems to work.