1- I'd like to use a validation rule for an input cell where the entry must be 7 or 8 alphanumeric characters long
2- at the start of the string Alphas used must be 1 or 2 characters and uppercase.
3- at the end of the string Numerics will always be 6 characters long.
4- The following type of entries are required to be validated FD456789 X256325 Z899666 DQ985421 FD000052
5-I have created a validation formula. it works fine except it cannot validate 2nd character as alphabate in the string. i used AP656569 and A5656569 for testing. it should allow only AP656569, but on the contrary it is allowing both strings.
Formula: =AND(OR(LEN(A3)=7,LEN(A3)=8),ISNUMBER(VALUE(RIGHT(A3,6))),IF(LEN(A3)=7,NOT(ISNUMBER(VALUE(LEFT(A3,1)))),ISTEXT(MID(A3,2,1))))
You may try:
=AND(AND(LEN(A1)>6,LEN(A1)<9,ISNUMBER(RIGHT(A1,6)*1),CODE(A1)>64,CODE(A1)<91),IF(LEN(A1)=8,AND(CODE(MID(A1,2,1))>64,CODE(MID(A1,2,1))<91),1))
=AND( - Let's check two things:
AND( - Check if multiple conditions are TRUE:
LEN(A1)>6 - Check if string is over 6 char.LEN(A1)<9 - Check if string in under 9 chars.ISNUMBER(RIGHT(A1,6)*1 - Check if 6 rightmost characters make up a numeric value.CODE(A1)>64,CODE(A1)<91 - Check if leftmost characters is in class [A-Z].IF( - Check the following:
LEN(A1)=8 - Check if the lengths is actually 8.
AND( - If TRUE then check the following:
CODE(MID(A1,2,1))>64,CODE(MID(A1,2,1))<91 - Check if 2nd char is in class [A-Z].1 - If the length is not false, it will still be 7, therefor we return a 1 (equal to TRUE), to not mess with our parent AND().You can apply this to your custom validation rule as a formula if you want to avoid false data, or as mentioned in the comments to conditional formatting if you want to be able to show false data after it being entered.
Alternatively, if you have Excel 2019 or higher, and you like code-golf you could use:
=AND(ISNUMBER(RIGHT(A1,6)*1),CODE(A1)>64,CODE(A1)<91,SWITCH(LEN(A1),7,1,8,AND(CODE(MID(A1,2,1))>64,CODE(MID(A1,2,1))<91),0))