Validating PAN (Indian Format)

[Rick Rothstein]

Here is the updated one.

=AND(LEN(A1)=10,SUMPRODUCT(--(--(ISNUMBER(MID(A1,{1,2,3,4,5,6,7,8,9,10},1)+0))={0, 0,0,0,0,1,1,1,1,0}))+SUMPRODUCT((CODE(MID(UPPER(A1 ),{1,2,3,4,5,10},1))>64)*(CODE(MID(UPPER(A1),{1,2,3,4,5,10},1))<91))=16)

This slightly shorter formula (using two less function calls) also appears to work...

=AND(LEN(A1)=10,SUMPRODUCT(--(ABS(77.5-(CODE(MID(UPPER(A1),{1,2,3,4,5,10},1))))<13))=6,SUMPRODUCT(--ISNUMBER(--MID(A1,{6,7,8,9},1)))=4)

[Haseeb A]

...
=AND(LEN(A1)=10,SUMPRODUCT(--(ABS(77.5-(CODE(MID(UPPER(A1),{1,2,3,4,5,10},1))))<13))=6,SUMPRODUCT(--ISNUMBER(--MID(A1,{6,7,8,9},1)))=4)

Rick,

As you know you can shorten 2nd SUMPRODUCT(--ISNUMBER with COUNT,

COUNT(--MID(A1,{6,7,8,9},1))

Another one with less functions,

=AND(COUNT(MID(A1,{6,7,8,9},1)+0)=4,COUNT(FIND(MID (UPPER(A1),{1,2,3,4,5,10},{1,1,1,1,1,999}),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))=6)

EDIT:

Using by a tilde "~" could avoid UPPER function,

=AND(COUNT(MID(A1,{6,7,8,9},1)+0)=4,COUNT(SEARCH("~"&MID(A1,{1,2,3,4,5,10},{1,1,1,1,1,999}),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))=6)