Data Validation - 3 Letters + 2 Numbers

[tlc53]

Hi,

Can someone please tell me where I am going wrong here?

=AND(LEN(A62)=5,ISTEXT(LEFT(A62,3),ISNUMBER(RIGHT(A62,2))))

Thanks!

 

[Rick Rothstein]

Can someone please tell me where I am going wrong here?

=AND(LEN(A62)=5,ISTEXT(LEFT(A62,3),ISNUMBER(0+RIGHT(A62,2))))

The RIGHT function returns a Text value, not a numeric value, so ISNUMBER will not see the return from the RIGHT function as a number even if it is composed of all digits. However, if you involve a Text number in a mathematical operation, then Excel will convert the Text number to a real number in order to be able to complete the mathematical operation. So, if you add 0 or multiply by 1, you will convert the Text number to a real number so that ISNUMBER can see it as a number. So, making the change I show in red will get that part of your formula working correctly. I want to point out, however, that what I highlighted in blue is not guaranteed to return TRUE only for letters (what your thread title said you wanted)... the ISTEXT function will return TRUE for things like A12, 3B4, 56C, and the like. Also, since its argument is a Text function, ISTEXT will return TRUE even if the LEFT function returns all digits (see the first part of this response). You will need a different test to do what you want... let me think about it for a bit.

 

[Rick Rothstein]

Give this formula a try...

=AND(LEN(A1)=5,IF(MID("AAA00",{1,2,3,4,5},1)="A",(MID(A1,{1,2,3,4,5},1)>="A")*(MID(A1,{1,2,3,4,5},1)<="Z"),ISNUMBER(-MID(A1,{1,2,3,4,5},1))))

 

[tlc53]

the ISTEXT function will return TRUE for things like A12, 3B4, 56C, and the like. Also, since its argument is a Text function, ISTEXT will return TRUE even if the LEFT function returns all digits (see the first part of this response). You will need a different test to do what you want... let me think about it for a bit.

Thank you very much for this!
I think allowing A12, 3B4 etc, may actually be what I want. I will need to test it out. Unfortunately I'm now being dragged away from my laptop, so I will need to look at it later. Thanks again and for the explanation of where I was going wrong.

 

[Rick Rothstein]

I think allowing A12, 3B4 etc, may actually be what I want.

Your thread title said 3 letters so that is what I responded to.

 

[tlc53]

Your thread title said 3 letters so that is what I responded to.

I thought 3 Letters + 2 Numbers was what I wanted but when I read your message, I realised I have an account G4S01. Didn't mean to be misleading, just an oversight which was made clear to me once I read your message. Sorry about that.
For this reason, I think your first formula will be best for this purpose. However, it is saying there is a formula error when I try to enter =AND(LEN(A62)=5,ISTEXT(LEFT(A62,3),ISNUMBER(0+RIGHT(A62,2))))
Can you perhaps see where I am going wrong?

 

[Peter_SSs]

I thought 3 Letters + 2 Numbers was what I wanted but when I read your message, I realised I have an account G4S01.

Given the change, can you clearly re-state in words exactly what the DV requirements are?

 

[tlc53]

Given the change, can you clearly re-state in words exactly what the DV requirements are?

3 letters or numbers followed by 2 numbers. Thanks.

 

[Peter_SSs]

3 letters or numbers followed by 2 numbers. Thanks.

I have assumed that any letters must be upper case. If so, try this for your custom DV formula.

=AND(LEN(A1)=5,OR(MAX(ABS(77.5-CODE(MID(A1,ROW(INDIRECT("1:3")),1))))<13,ISNUMBER(-MID(A1,ROW(INDIRECT("1:3")),1))),ISNUMBER(-MID(A1,ROW(INDIRECT("4:5")),1)))

 

[tlc53]

I have assumed that any letters must be upper case. If so, try this for your custom DV formula.

=AND(LEN(A1)=5,OR(MAX(ABS(77.5-CODE(MID(A1,ROW(INDIRECT("1:3")),1))))<13,ISNUMBER(-MID(A1,ROW(INDIRECT("1:3")),1))),ISNUMBER(-MID(A1,ROW(INDIRECT("4:5")),1)))

Wow, a lot more complicated than I had imagined. You were correct to assume the letters should be in caps. I tested it out and it works perfectly. Thanks so much