Conditional Formatting Based on Specific Number/Letter Combinations

[engineermeister]

Hi all,

I've been looking around but I can't find an easy solution to this one based on some other answers in threads, so I'm putting this out there. I think this is me being rather stupid more than anything else, but we'll see!

I'm creating a column in which the user has to enter a UK vehicle registration plate, of (for example) "MM45 TGR" - 2 letters, 2 numbers, a space and then 3 letters at the end. I want the cell to be highlighted if the entered text isn't of this format; just as a nudge to make people check what they've entered (not all plates fit that format but the vast majority do).

I feel that the required formula is somewhere along the lines of:

=NOT(AND(ISTEXT(LEFT(A1,1,2))),ISNUMBER(LEFT(A1,3,4)*1),ISTEXT(RIGHT(A1,1,2,3)))

but I know this not to be correct as a) it's a bodge from another thread that I've tried to adapt without quite fully understanding and b) Excel tells me so.

All help appreciated!

[Richard Buttrey]

Just going out at the moment so haven't got time to test it all out but the way I'd do it with some OR and AND conditions which test the code values of each position. i.e. letters A-Z must between codes 65-90 inclusive, a-z between 97-122 inclusive and and 1-9 between 49-57.

One way to eliminate a space if one occurs is to first use SUBSTITUTE(text," ","").

I'd be inclined to use a helper column to work it all out and then use the result of the helper column in the CF.

[benishiryo]

welcome to the forum. this could be pretty lengthy. i'll explain parts of it and it will be repetitive along the way. that is a certain code for each character in Excel. so the capital "A" is character 65. type A in cell A1 and use this formula in B1
=CODE(A1)

and Z is character 90. so in order to check if they are alphabets, i check them in UPPER case and see if they fall between 65 and 90. this checks for first letter.
=AND(CODE(UPPER(LEFT(A1,1)))>=65,CODE(UPPER(LEFT(A1,1)))<=90)

for first two letters (repeat and changed the red portion:

Please Login or Register  to view this content.

to check for number, ensure you convert it to a number by two negations before using ISNUMER:

Please Login or Register  to view this content.

check for space:

Please Login or Register  to view this content.

check for last 3 letters:

Please Login or Register  to view this content.

addition:
lastly, add a NOT to your conditional formatting.

Please Login or Register  to view this content.

[shukla.ankur281190]

Try

in conditional formatting.

Please Login or Register  to view this content.

[benishiryo]

just to also explain to OP shukla's methods to see if it meets your needs, he is ascertaining 4 parts:
1. the first 2 characters are NOT numbers
ISERROR(MATCH(TRUE,ISNUMBER(--MID(LEFT(A1,2),ROW(INDIRECT("1:"&LEN(LEFT(A1,2)))),1)),0))

2. the 3rd and 4th characters ARE numbers
ISNUMBER(MID(A1,3,2)+0)

3. there is a space in the 5th character
NOT(ISERROR(SEARCH(" ",A1)=5))

4. the last 3 characters are NOT numbers
ISERROR(MATCH(TRUE,ISNUMBER(--MID(RIGHT(A1,3),ROW(INDIRECT("1:"&LEN(RIGHT(A1,3)))),1)),0))

you have to decide if the checking for alphabets meets your needs because he is only checking if they are NOT numbers. That means it will pass even if they are symbols, spaces, small casing. if it doesn't matter, then it's a much easier approach.
it could be:
~ 45 abc

[engineermeister]

Brilliant, thanks very much chaps! These solutions all seem to have worked upon trial, so chapeau to you all!

Luckily just checking for it not being a number is good enough - as it is just nudging the poor sod doing data entry to check again, which hopefully a highlight will do!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.