Indian PAN Format

[Oh!Calcutta]

Hi All,

Can anyone help me frame a Data Validation formula for Indian PAN in a column? The data structure is 5 Alphabets 4 numbers and an Alphabet (i.e. field length is 10).

Thanks in advance.

 

[Mindpsyche]

Cell H9 is where you will enter the PAN number.

Try this formula under Data Validation - > Custom -

=AND(ISERROR(VALUE(MID(H9,6,4)))=FALSE,ISERROR(VALUE(LEFT(H9,2)))=TRUE,ISERROR(VALUE(RIGHT(H9,1)))=TRUE)

 

[ashokpai]

Hi,

Please find the formula

=IF(LEN(A1)=10,IF(AND(SUMPRODUCT(--(CODE(MID(A1,{1,2,3,4,5,10},1))>=65),--(CODE(MID(A1,{1,2,3,4,5,10},1))<=90))=6,SUMPRODUCT(--(CODE(MID(A1,{6,7,8,9},1))>=48),--(CODE(MID(A1,{6,7,8,9},1))<=57))=4),"OK","NOT OK"),"NOT OK")

Regards,

Ashok Pai

 

[ownbypss]

Cell H9 is where you will enter the PAN number.

Try this formula under Data Validation - > Custom -

=AND(ISERROR(VALUE(MID(H9,6,4)))=FALSE,ISERROR(VALUE(LEFT(H9,2)))=TRUE,ISERROR(VALUE(RIGHT(H9,1)))=TRUE)

Hi,

I need validation in which starting 4 will be alphabetical and last 10 will be numeric. Please help me out.

 

[Joe4]

I need validation in which starting 4 will be alphabetical and last 10 will be numeric. Please help me out.

Duplicate: https://www.mrexcel.com/forum/excel-questions/1056864-excel-validation-formula.html

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will typically be locked or deleted (rule 12 here: Forum Rules).

Anyone wishing to answer this question, please do so on the other thread.