Tom Ogilvy wrote:
What array formula were you refering to?

sorry for my insufficient and strange explanation.
it is my array formula that has not worked in Data-Validation
like this:

=OR(LEFT(C10,6)={"A-TFG-","B-TFG-"})*AND(ISNUMBER(--MID(C10,{7,8,9,10},1)))
*AND(ABS(CODE(UPPER(MID(C10,{11,12},1)&"a"))-77.5)<13)*(LEN(C10)<=12)
+AND(ABS(CODE(UPPER(MID(C10,{1,2,3},1)))-77.5)<13)*(LEFT(C10,3)<"TFG")
*AND(ISNUMBER(--MID(C10,{4,5,6,7},1)))*(LEN(C10)=7)

or a little bit shorter

=OR(LEFT(C10,6)={"A-TFG-","B-TFG-"})*AND(ISNUMBER(--MID(C10,{7,8,9,10},1))
,ABS(CODE(UPPER(MID(C10,{11,12},1)&"a"))-77.5)<13,LEN(C10)<13)
+AND(ABS(CODE(UPPER(MID(C10,{1,2,3},1)))-77.5)<13,LEFT(C10,3)<"TFG"
,ISNUMBER(--MID(C10,{4,5,6,7},1)),LEN(C10)=7)

i think that it is necessary to test characters one by one.
without array, a long formula will be needed.

=((LEFT(C10,6)="A-TFG-")+(LEFT(C10,6)="B-TFG-"))
*ISNUMBER(--MID(C10,7,1))*ISNUMBER(--MID(C10,8,1))
*ISNUMBER(--MID(C10,9,1))*ISNUMBER(--MID(C10,10,1))
*(ABS(CODE(UPPER(MID(C10,11,1)&"a"))-77.5)<13)
*(ABS(CODE(UPPER(MID(C10,12,1)&"a"))-77.5)<13)
*(LEN(C10)<=12)
+(ABS(CODE(UPPER(MID(C10,1,1)))-77.5)<13)
*(ABS(CODE(UPPER(MID(C10,2,1)))-77.5)<13)
*(ABS(CODE(UPPER(MID(C10,3,1)))-77.5)<13)
*(LEFT(C10,3)<"TFG")
*ISNUMBER(--MID(C10,4,1))*ISNUMBER(--MID(C10,5,1))
*ISNUMBER(--MID(C10,6,1))*ISNUMBER(--MID(C10,7,1))
*(LEN(C10)=7)

being divided into multiple names, this will work in Data-Validation
instead of a UDF. but it is more difficult and time$B!(Bconsuming.

--
Regards,

okaizawa