Ad Free CAclubindia and Daily Updates on your Mobile. Subscribe Now!

Validating pan in excel

Anand (student) (116 Points)

14 July 2013

VALIDATING PAN IN EXCEL SO THAT ERROR CAN REDUCE

Reply
Follow
Share

5 Replies

bujjulu (e-TDS + IT return filer Hyderabad) (731 Points)
Replied 15 July 2013

could not understand.....

Upgrade to CAclubindia Pro. Subscribe Now!

CA Vanamala Phani Kumar (Proprietor of M/s Vanamala and Co) (972 Points)
Replied 16 July 2013

Anand (student) (116 Points)
Replied 18 July 2013

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.

bujjulu (e-TDS + IT return filer Hyderabad) (731 Points)
Replied 18 July 2013

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.

Hemant Rathod (Director) (22 Points)
Replied 10 May 2014

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