Data Validation - 3 Letters + 2 Numbers

tlc53

Active Member
Joined
Jul 26, 2018
Messages
399
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!
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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.
 
Upvote 0
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))))
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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?
 
Last edited:
Upvote 0
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)))
 
Upvote 0
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 :)
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top