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
Bookmarks