custom data validation in Excel for alpha-numeric-

问题:

I want to force users to enter data in a specific cell in a sequence like ABCDE1234F

i.e. first five characters must me letters then four digits and last must be a letter using custom data validation.

回答1:

It is quite long:

=AND(ISNUMBER(SUMPRODUCT(SEARCH("~"&MID(A1,ROW($1:$5),1),"abcdefghijklmnopqrstuvwxyz"))),ISNUMBER(SUMPRODUCT(SEARCH("~"&MID(A1,ROW($6:$9),1),"0123456789"))),ISNUMBER(SUMPRODUCT(SEARCH("~"&MID(A1,10,1),"abcdefghijklmnopqrstuvwxyz"))),LEN(A1)=10)

It goes through each of the required text characters and checks if it is a number or a string. Then it test the full range of numbers for text. It also ensures that it is ten characters long.

回答2:

You may try following formula

=AND(ISTEXT(LEFT(A1,5)),ISNUMBER(MID(A1,6,4)*1),ISTEXT(MID(A1,10,1)),IF(LEN(A1)=10,TRUE,FALSE))