Validating PAN (Indian Format)

[Admin]

Hi All,

Here is a method to validate PAN in Excel. The rules for validation are as follows:


• Length should be 10
• First 5 character should be text [A-Za-z]
• Next 4 should be number
• Last character should be text [A-Za-z]


and the formula will be

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

More solutions are welcome