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!

 

[Peter_SSs]

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

Glad it was what you wanted.

 

[Rick Rothstein]

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)))

Peter, am I mistaken or is your formula returning TRUE for situations where one of the three first characters is non-alphanumeric? For example 1-2345 or 12?45 or -1234 and so on.

While you ponder that, here is a formula I came up with which I think is foolproof (I'm sure I'll end up having to eat my words on that)...

=ISNUMBER(SUM(FIND(MID(A1,ROW(INDIRECT("1:3")),1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"))+SUM(FIND(MID(A1,ROW(INDIRECT("4:5")),1),"0123456789")))

 

[Peter_SSs]

Peter, am I mistaken or is your formula returning TRUE for situations where one of the three first characters is non-alphanumeric? For example 1-2345 or 12?45 or -1234 and so on.

Thanks Rick
I thought I had tested such values but clearly not, as looking back at the logic of my formula you are perfectly correct

(I'm sure I'll end up having to eat my words on that)...

Also correct, but I think only in that you have omitted the 5-character test.


So, with a variation to shorten it up even with that additional test, maybe this ..

=(LEN(A1)=5)*(COUNT(FIND(MID(A1,ROW(INDIRECT("1:5")),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789",IF(ROW(INDIRECT("1:5"))<4,1,27)))=5)

 

[Rick Rothstein]

...but I think only in that you have omitted the 5-character test.

I forgot to include that??? Really??? I am so embarrassed!

So, with a variation to shorten it up even with that additional test, maybe this ..

=(LEN(A1)=5)*(COUNT(FIND(MID(A1,ROW(INDIRECT("1:5")),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789",IF(ROW(INDIRECT("1:5"))<4,1,27)))=5)

I like this variation.

 

[tlc53]

Well above my pay grade
Thank you both! Works great.

 

[Peter_SSs]

Well above my pay grade

That is why we get so highly paid here.

Thank you both! Works great.

You're welcome.