Conditional formatting for 1 alpha followed by 6 numeric characters

[Computerman]

I am trying to implement conditional formatting in a column so that if the first character is alpha in the range A through Z, or a through z, the fill color is green, if not the fill color is red. I started with =OR(LEFT(S2,1)="A"),(LEFT(S2,1)="B") etc with the fill color set to Green, then the same formulas with Not Equal (<>) in place of the equals and the fill color set to Red. This works, but there must be a better way the check that the first character is an A through a Z without writing 52 conditional format steps.

Thank You,
Computerman

 

[Tetra201]

Try

=AND(CODE(UPPER(LEFT(S2)))>64,CODE(UPPER(LEFT(S2)))<91)

Or even shorter:

=AND(CODE(UPPER(S2))>64,CODE(UPPER(S2))<91)

 

[pgc01]

Hi

Try also:

=AND(LEFT(S2)>="a",LEFT(S2)<="z")

 

[Computerman]

Thank you to both Tetra201 and pgc01. I decided to go with the code that pgc01 provided as it is more 'readable' for me. 6 months from know I will know what a "a" is, but not necessarily what a "Code64" is. I also added code to turn the cell Green if an Alpha is entered first and 6 characters are entered for a total of 7 characters. However, I tried to put in code that if the first letter is not an "a" through "z" the cell will turn Red, and other code so that if the len of the contents of the cell are less than 7 or more than 7 the cell will turn Red, but they are not working. When I enter a value that is not a letter followed by 6 charaters the cell reverts to the original cell color. I have other columns where I am just checking the number of characters and they work correctly, if the LEN of what is entered is either less than or more than the correct number the cell turns Red, if the LEN is correct the cell turns Green. Could the order that I apply the Conditional Formatting rules be the cause?

Thanks again,
Computerman

 

[Computerman]

RESOLVED,
The order that the rules are in, do matter. Now my first rule turns the cell Red if the LEN is less than 7. Second rule turns the cell Red if the LEN is greater than 7. Third rule turns the cell Red if it does not start with an 'a' through 'z'. Fourth rule turns the cell Green if it starts with an 'a' through z'. Fifth rule turns the cell Green if the LEN equals 7. Now the cells in the column react to the data entered in them perfectly.
Thanks,
Computerman

 

[pgc01]

I'm glad we could help. Thanks for the feedback.