Search code examples
excelvalidationexcel-formulaalphanumeric

custom data validation in Excel for alpha-numeric-alpha value

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.

Solution

  • 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.