Data validation of alpha plus four digits batch number

[MarkHH]

I have written a formula to do data validation that an five character batch code complies to the format "A1234", however is seems a bit long winded and wonder if anyone can suggest a more streamlined solution - must not involve arrays unfortunately.
In position 1, The first character must comply with a list of legal letters(only upper case allowed)in named range rngAplha
ABCDEFGHKLMNPQRSTUVWX
The numbers in positions 2 to 5 must all be integers, no decimals, with the allowable values of the four numbers are from 0001 to 9999.

This formula works:

=IFERROR(AND(LEN(C3)=5,IF(COUNTIF(rngAlpha,LEFT(C3,1))=1,EXACT(LEFT(C3,1),UPPER(LEFT(C3,1)))),INT(RIGHT(C3,4))=RIGHT(C3,4)*1,RIGHT(C3,4)*1>0),"Batch No. error")

Brief functional description:
IFERRORIFERROR (AND(1,2,3,4,5), "Batch No. error")
AND 1LEN(G3)=5
AND 2IF(COUNTIF(rngAlpha,LEFT(G3,1))=1,EXACT(LEFT(G3,1),UPPER(LEFT(G3,1))))
AND 3INT(RIGHT(G3,4))=RIGHT(G3,4)*1
AND 4RIGHT(G3,4)*1>0
AND 5ISERR(SEARCH(".",G3,1))

The numbers are the biggest problem, individually and as a string.

Thanks in advance for your creative responses!

Mark

 

[NARAYANK991]

Hi ,

The length of a DV expression depends on the number of rules you want to implement.

See if the following implements all the rules that you want :

=AND(LEN(D5) = 5, ISNUMBER(RIGHT(D5,4)+0), ISNUMBER(FIND(LEFT(D5),rngAlpha)), ISNUMBER(RIGHT(D5,4)+0) * (INT(RIGHT(D5,4)+0) = RIGHT(D5,4)+0))

The rules implemented in each section are :

1. Number of characters in the expression = 5

2. The right-most 4 characters evaluate to a number

3. The left-most character is in the named range rngAlpha

4. The right-most 4 characters do not contain a decimal point

Narayan

 

[shrivallabha]

For numeric part you can also try:
=NOT(ISERROR(SUM(FREQUENCY(--MID(A2,{2,3,4,5},1),{0,1,2,3,4,5,6,7,8,9}))))

 

[NARAYANK991]

For numeric part you can also try:
=NOT(ISERROR(SUM(FREQUENCY(--MID(A2,{2,3,4,5},1),{0,1,2,3,4,5,6,7,8,9}))))

Hi ,

That will have to be rewritten for a DV formula.

Narayan

 

[bosco_yip]

Another option formula for DV

=AND(LEN(A1)=5,ISNUMBER(FIND(LEFT(A1),"ABCDEFGHKLMNPQRSTUVWX")),COUNT(-MID(A1,ROW(INDIRECT("2:5")),1))=4)

p.s.

I am using Excel 2016, and my Data Validation doesn't accept Range Name and Constant Array.

Regards
Bosco

 

[shrivallabha]

Hi ,

That will have to be rewritten for a DV formula.

Narayan

You are correct, Narayan. What could be the possible reason that DV formula box doesn't want that sort of formula structure?

I too faced issues with named range in Excel 2016 like Bosco. Here's how it worked for me finally:
=AND(LEN(D5)=5,ISNUMBER(FIND(LEFT(D5),"ABCDEFGHIJKLMNOPQRSTUVWXYZ",1)),NOT(ISERROR(SUM(FREQUENCY(--MID(D5,ROW($A$2:$A$5),1),ROW($A$1:$A$10)-1)))))

 

[NARAYANK991]

You are correct, Narayan. What could be the possible reason that DV formula box doesn't want that sort of formula structure?

I too faced issues with named range in Excel 2016 like Bosco. Here's how it worked for me finally:
=AND(LEN(D5)=5,ISNUMBER(FIND(LEFT(D5),"ABCDEFGHIJKLMNOPQRSTUVWXYZ",1)),NOT(ISERROR(SUM(FREQUENCY(--MID(D5,ROW($A$2:$A$5),1),ROW($A$1:$A$10)-1)))))

Hi ,

I have no idea , but when I entered your initial formula , Excel gave an error message that literals cannot be used in a DV formula ; thus , usage of arrays such as {2,3,4,5} and {0,1,2,3,4,5,6,7,8,9} is forbidden.

The ways around this are either to use named ranges or to use the ROW(...) construct as you have done.

Narayan