Data validation format Letter number letter number etc.

Hi,

I had a quick look through some of the previous post and couldn't find a solution to my problem.

I want to apply Data Validation to a cell, so that only the following combination of letters and numbers can be entered.

Letter Letter Number Number Number Number Number Number Letter.
e.g AB123456C.

Any help would be greatly appreciated,
Thanks,
Dave

Would it be OK if the wrong cells were highlighted in, say a red colour?

If so, this formula applied as a conditional format to your cell will achieve that type of result for cell A1:

Please Login or Register  to view this content.

You would need to set the highlight colour to whatever one you want.

If a user then enters say ABC123456D, the cell will be highlighted in the selected colour.

A slight tweak allows the formula to be used in data validation:

Use the following formula in the Data validation | Custom setting:

Please Login or Register  to view this content.

IMPORTANT NOTE: The user must enter letters as lower case using this validation setting.

Afraid my original formula won't do the job- the data a7123456c won't be caught as an error as Excel will read the 7 as text without a further check.

Perhaps using a helper column besides the cell where the data is entered will help eg if you enter this formula into the helper cell (say A3 or C3) beside where the data will be entered (B3) you could then apply a data validation test to check if the helper cell reads TRUE or FALSE, with appropriate feedback as required:

=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)),ISNUMBER(0+MID(B3,3,6)))

{The foregoing allows upper and lower case letters to be entered into B3}

You'd then set the data validation to Custom | =AND((C3=TRUE),OR(RIGHT(B3,1)="A",RIGHT(B3,1)="B",RIGHT(B3,1)="C",RIGHT(B3,1)="D",RIGHT(B3,1)="F",RIGHT(B3,1)="M")). If a user misenters a character, this will be trapped.

It also will prevent a user from entering anything other than A,B,C,D,F or M as the last character

To ensure the length of the data is correct, change the Data validation setting to the following:

Please Login or Register  to view this content.

Thanks for spotting the typo

That's probably a VBA solution you're looking for.

I'm not really competent in VBA but I'd say if you either search the forum/ create a new thread you'll find someone who'll have the answer for that.

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:

Please Login or Register  to view this content.

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):

Please Login or Register  to view this content.

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.