Custom data validation to assess last character and length combined

[BlueDynamo]

Hi all. I want to setup a custom data validation to assess the contents of a range of cells against the following criteria:

1. Contents of the cell must be unique compared to any other.
2. Contents of the cell must end in any letter (between A and Z) if 7 characters long.
3. Contents of the cell must end in any number (between 0 and 9) if 6 characters long.

I’ve managed to create a formula to assess the first criteria and the maximum text length only:

=AND(COUNTIF($J$6:$J$255,J6)=1,LEN(J6)<=7)

However, I am struggling to create a formula to fully define the last two criteria. Can anyone help please?

 

[mart37]

For the last character you can use: RIGHT(J6,1)
For test if character is between a range use: CODE()
For testing 7th character is A..Z: AND(LEN(J6)=7,CODE(RIGHT(J6,1))>=65,CODE(RIGHT(J6,1))<=90)
For testing 6th character is 0..9: AND(LEN(J6)=6,CODE(RIGHT(J6,1))>=48,CODE(RIGHT(J6,1))<=57)

 

[BlueDynamo]

Many thanks.

I've tried to combine these formulas above into a single formula to satisfy the three criteria but am now getting an error message:

=AND(COUNTIF($J$6:$J$255,J6)=1,(LEN(J6)=7,CODE(RIGHT(J6,1))>=65,CODE(RIGHT(J6,1))<=90),(LEN(J6)=6,CODE(RIGHT(J6,1))>=48,CODE(RIGHT(J6,1))<=57))

Have I made a mistake?

 

[mart37]

To many ( ).
Also you have everywhere used the AND function. The last two conditions needs an OR-relation: 2 OR 3
Also forgotten: LEN(J6)=7
When <6 are there no restrictions?

 

[mart37]

Sorry LEN(J6)=7 is not missing.

 

[BlueDynamo]

There should not be any cells that contain <6 characters.

I'm a bit confused by your comment about too many brackets and the OR function for the last two conditions. How does this fit into the formula?

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this custom DV formula is what you want.

=AND(COUNTIF(J$6:J$255,J6)=1,OR(AND(LEN(J6)=7,ABS(77.5-CODE(RIGHT(J6,1)))<13),AND(LEN(J6)=6,ISNUMBER(RIGHT(J6,1)+0))))