Validating pan in excel

could not understand.....

DEAR BUJJLU,

IN THE FILE YOU CAN SEE THREE METHODS.

IN FIRST METHOD

I M SHOWING YOU CODING FOR CREATING FUNCTION THROUGH EXCEL MACRO, ONE IS "VALIDATEPAN" AND ANOTHER IS "CHECKATOZ". WHEN YOU CREATED THESE FUNCTION YOU CAN USE IT VERY EASILY LIKE OTHER FUNTIONS IN EXCEL

IN SECOND METHOD

I M SHOWING YOU A COMBINATION OF FUNCTION TO ENSURE NAME OF DEDUCTEE IS WRITTEN IN UPPERCASE ONLY

IN THIRD METHOD

I M SHOWING YOU A SIMPLE FORMULA TO ENSURE THAT LENGTH OF PAN NUMBER IS 10 CHARACTERS ONLY.

Mr. Anand I did not tell you I could not understand . I told you I could not see the file at all.

If I can see the file then there may not be any necessity for your explanaion. Your clarification s will be of use only when I can open the file. 

Thank you.

I have also worked out formula  if useful to any one 

=IF(LEN(Tbl_26Q[[#This Row],[Pan]])=10,
IF(ISNUMBER(VALUE(MID(Tbl_26Q[[#This Row],[Pan]],6,4)))=TRUE,
IF(ISERROR(INDIRECT(MID(Tbl_26Q[[#This Row],[Pan]],1,1) & "1"))=FALSE,
IF(ISERROR(INDIRECT(MID(Tbl_26Q[[#This Row],[Pan]],2,1) & "1"))=FALSE,
IF(ISERROR(INDIRECT(MID(Tbl_26Q[[#This Row],[Pan]],3,1) & "1"))=FALSE,
IF(ISERROR(INDIRECT(MID(Tbl_26Q[[#This Row],[Pan]],5,1) & "1"))=FALSE,
IF(ISERROR(INDIRECT(MID(Tbl_26Q[[#This Row],[Pan]],10,1) & "1"))=FALSE,
IF(MID(Tbl_26Q[[#This Row],[Pan]],4,1)="P","OK",
IF(MID(Tbl_26Q[[#This Row],[Pan]],4,1)="H","OK",
IF(MID(Tbl_26Q[[#This Row],[Pan]],4,1)="C","OK",
IF(MID(Tbl_26Q[[#This Row],[Pan]],4,1)="F","OK",
"Error-4char is Not P/H/C/F")))),"Error-10 Char not Alpha"),"Error-5 Char not Alpha"),"Error-3 Char not Alpha"),"Error-2 Char not Alpha"),"Error-1 Char not Alpha"),"Error-6to9 Char not Number"), "Error-Lenght Is not 10")

i have created Table Tbl_26Q and Row name Pan u can do modication as per you need 

i have added only 4 validation for 4th aphabate u can and more also 

C — Company
F — Firm
H — HUF(Hindu Undivided Family)
P — Person
 

A — Association of Persons (AOP)
B — Body of Individuals (BOI)
G — Government
J — Artificial Juridical Person
L — Local Authority
T — AOP (Trust)

Regrds 

Hemant