Thread: Data Validation - 3 Letters + 2 Numbers Thanks:  1 Post #5345669 (1) Likes:  2 Post #5345669 (1)Post #5347430 (1)

1. Data Validation - 3 Letters + 2 Numbers

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!  Reply With Quote

2. Re: Data Validation - 3 Letters + 2 Numbers Originally Posted by tlc53 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.  Reply With Quote

3. Re: Data Validation - 3 Letters + 2 Numbers

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))))  Reply With Quote

4. Re: Data Validation - 3 Letters + 2 Numbers Originally Posted by Rick Rothstein 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.  Reply With Quote

5. Re: Data Validation - 3 Letters + 2 Numbers Originally Posted by tlc53 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.  Reply With Quote

6. Re: Data Validation - 3 Letters + 2 Numbers Originally Posted by Rick Rothstein 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?  Reply With Quote

7. Re: Data Validation - 3 Letters + 2 Numbers Originally Posted by tlc53 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?  Reply With Quote

8. Re: Data Validation - 3 Letters + 2 Numbers Originally Posted by Peter_SSs 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.  Reply With Quote

9. Re: Data Validation - 3 Letters + 2 Numbers Originally Posted by tlc53 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)))  Reply With Quote

10. Re: Data Validation - 3 Letters + 2 Numbers Originally Posted by Peter_SSs 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   Reply With Quote

User Tag List

Tags for this Thread

data, data validation, formula, validation, wrong  Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•