Thread: Data validation format Letter number letter number etc.

OK- it seems that Excel interprets the letter e when preceded and followed by a number as an exponent [could conceivably be useful to know :-) ]

Change the 'big formula' above to the following (new bit in red) and it should cater for that situation:

=AND(OR(AND(CODE(LOWER(LEFT(B3,1)))>=97,CODE(LOWER(LEFT(B3,1)))<=122),AND(CODE(LEFT(B3,1))>=65,CODE( LEFT(B3,1))<=90)),OR(AND(CODE(LOWER(MID(B3,2,1)))>=97,CODE(LOWER(MID(B3,2,1)))<=122),AND(CODE(MID(B3,2,1))>=65,CODE(MID(B3,2,1))<=90)),OR(AND(CODE(LOWER(RIGHT(B3,1)))>=97,CODE(LOWER(RIGHT(B3,1)))<=122 ),AND(CODE(RIGHT(B3,1))>=65,CODE(RIGHT(B3,1))<=90)),ISERROR(FIND("e",MID(B3,3,6))),ISNUMBER((1*MID(B3,3,6))))

Appreciate this was resolved but just came across in a Search... and on the off chance you were still looking to do without helpers... and to help enforce Upper Case...

Originally Posted by DaveM13

After looking through the forums a bit more, i found the following formula posted by donkeyote, which works to some extent, but not completly.

=AND(AND(CODE(LEFT(B3,1))>=65,CODE(LEFT(B3,1))<=90),AND(CODE(MID(B3,2,1))>=65,CODE(MID(B3,2,1))<=90) ,AND(CODE(RIGHT(B3,1))>=65,CODE(RIGHT(B3,1))<=90),ISNUMBER(0+MID(B3,3,6)))

presumably the above fails re:

Originally Posted by DaveM13

Also, as a side not, the last letter in the national insurance number must only be on of the following letters - A B C D F or M.

and also the issue of E within MID(B3,3,6) ?


If so then this validation rule can be adapted accordingly... first create a named range of the permissable last letters, eg:

Sheet1!Z1 contains string: A,B,C,D,F,M
Named Range: =_lastletters
RefersTo: =Sheet1!$Z$1
(you don't need to use a Named Range per se but using a Named Range does permit you to store the string of interest on a sheet other than that on which the validation is being applied)


Your B3 Validation then becomes (incorporating issue of E):

=AND(AND(CODE(LEFT(B3,1))>=65,CODE(LEFT(B3,1))<=90),AND(CODE(MID(B3,2,1))>=65,CODE(MID(B3,2,1))<=90),ISNUMBER(FIND(RIGHT(B3,1),_lastletters)),ISNUMBER(0+SUBSTITUTE(UPPER(MID(B3,3,6)),"E","Z")))

If you wish to make case insensitive then encase all initial string references within UPPER functions - that being said your earlier post would imply you specifically want to enforce Upper Case - this should be catered for via the Validation as Lower Case letters will not be accepted.