+ Reply to Thread
Results 1 to 14 of 14

Permanent Account Number (PAN) Validation

  1. 08-20-2013, 01:14 AM #1
    keshavmundhra
    keshavmundhra is offline
    Registered User
    Join Date
    08-10-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    7

    Permanent Account Number (PAN) Validation

    Hi All,

    Need the formula to validate Indian Permanent Account Number (PAN) in Excel. The rules for validation should be as below:

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

    I am currently using the following formula in Data validation -

    =AND(NOT(SUM(--ISNUMBER(VALUE(MID(A1,ROW(INDIRECT("1:5")),1))))),SUM(--ISNUMBER(INT(MID(A1,ROW(INDIRECT("6:9")),1))))=4,NOT(--ISNUMBER(RIGHT(A1,1))),LEN(A1)=10)

    But this has certain problems - 1) It allows space as a character for the text part and 2) Numbers are also allowed for the last digit.

    Thanks.
    Regards
    Reply With QuoteRegister To Reply
  2. 08-20-2013, 03:21 AM #2
    ramananhrm
    ramananhrm is offline
    Valued Forum Contributor
    Join Date
    09-15-2011
    Location
    Chennai, India
    MS-Off Ver
    Excel 2010
    Posts
    436

    Re: Permanent Account Number (PAN) Validation

    I have tried with Helper columns.
    Attached Files Attached Files
    Last edited by ramananhrm; 08-20-2013 at 03:26 AM.
    Please click 'Add reputation', if my answer helped you.
    Reply With QuoteRegister To Reply
  3. 08-20-2013, 05:08 AM #3
    rajeshturaha
    rajeshturaha is offline
    Forum Contributor rajeshturaha's Avatar
    Join Date
    08-22-2012
    Location
    Assam, India
    MS-Off Ver
    Excel 2003, 2007
    Posts
    236

    Re: Permanent Account Number (PAN) Validation

    Try This

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Rgd
    RT
    If my answer(s) helped you, please add me reputation by click on *
    Reply With QuoteRegister To Reply
  4. 08-21-2013, 05:48 AM #4
    keshavmundhra
    keshavmundhra is offline
    Registered User
    Join Date
    08-10-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Permanent Account Number (PAN) Validation

    Hi rajesh......this does not work......only solves the space problem.......but then it allows text and number everywhere.....


    Quote Originally Posted by rajeshturaha View Post
    Try This

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 08-21-2013, 05:49 AM #5
    keshavmundhra
    keshavmundhra is offline
    Registered User
    Join Date
    08-10-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Permanent Account Number (PAN) Validation

    Hi....thanks......but would you be able to consolidate this and give me a one formula in data validation......i am a bit new to these things.....hence requesting the consolidated formula....





    Quote Originally Posted by ramananhrm View Post
    I have tried with Helper columns.
    Reply With QuoteRegister To Reply
  6. 10-07-2013, 12:07 PM #6
    ANJUMKUL2786
    ANJUMKUL2786 is offline
    Registered User
    Join Date
    10-07-2013
    Location
    delhi
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Permanent Account Number (PAN) Validation

    Thank you for giving these formula. But in indian pan the fourth alphabet denotes to p-individual, c-company, f-firm, h-huf etc.can anyone develop a formula for this purpose
    Reply With QuoteRegister To Reply
  7. 10-07-2013, 05:27 PM #7
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Permanent Account Number (PAN) Validation

    If all letters are UPPER CASE this will work:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    If the letters are lower case then this will work:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Both of these formulae check character by character the code number for each character to see if it falls between the values for upper or lower letters and numerals.

    This is a combination of the two above but will not allow a mix of upper and lower case:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Last edited by newdoverman; 10-07-2013 at 05:35 PM.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W
    Reply With QuoteRegister To Reply
  8. 10-07-2013, 06:25 PM #8
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Permanent Account Number (PAN) Validation

    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    for upper case

    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    for mixed upper or lower case

    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    lower case only
    Last edited by martindwilson; 10-07-2013 at 06:34 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula
    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC
    Reply With QuoteRegister To Reply
  9. 01-05-2014, 07:12 AM #9
    Hariprasad2k6
    Hariprasad2k6 is offline
    Registered User
    Join Date
    01-05-2014
    Location
    tirupati
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Permanent Account Number (PAN) Validation

    Sir i need the fourth letter in PAN number must be character "P" how to do it.
    Reply With QuoteRegister To Reply
  10. 01-05-2014, 08:06 AM #10
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,453

    Re: Permanent Account Number (PAN) Validation

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    How to paste Power Query code ( by Olly)
    Reply With QuoteRegister To Reply
  11. 01-16-2014, 08:33 AM #11
    itsviju
    itsviju is offline
    Registered User
    Join Date
    05-17-2010
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Permanent Account Number (PAN) Validation

    =if(mid(y2,4,1)="p",if(len(y2)=10,if(and(sumproduct(--(code(mid(y2,{1,2,3,4,5,10},1))>=65),--(code(mid(y2,{1,2,3,4,5,10},1))<=90))=6,sumproduct(--(code(mid(y2,{6,7,8,9},1))>=48),--(code(mid(y2,{6,7,8,9},1))<=57))=4),"ok","not ok"),"not ok"),"no ok")
    Reply With QuoteRegister To Reply
  12. 10-09-2014, 11:19 AM #12
    spymatemate
    spymatemate is offline
    Registered User
    Join Date
    10-09-2014
    Location
    Indore,MP
    MS-Off Ver
    MS Office 2013
    Posts
    2

    Re: Permanent Account Number (PAN) Validation

    actually all the formulas shown here are to be added in each cell and it is showing error, i want to validate that PAN I entered is correct or not. Means PAN is like AAACP0209I, so i want that first five characters should be alphabet, next four are numbers and last is again alphabet. If i type in format AACP0209I, then it should show error that i should type first 5 alphabet,next 4 number and last alphabet.
    Reply With QuoteRegister To Reply
  13. 10-09-2014, 12:39 PM #13
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Permanent Account Number (PAN) Validation

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Reply With QuoteRegister To Reply
  14. 07-28-2017, 09:16 AM #14
    spymatemate
    spymatemate is offline
    Registered User
    Join Date
    10-09-2014
    Location
    Indore,MP
    MS-Off Ver
    MS Office 2013
    Posts
    2

    Re: Permanent Account Number (PAN) Validation

    To Validate PAN(Indian Version), you need to use formula in a cell, referencing the cell in which you are going to enter PAN.

    =AND(LEN(A1)=10,SUMPRODUCT(--(ABS(77.5-(CODE(MID(UPPER(A1),{1,2,3,4,5,10},1))))<13))=6,SUMPRODUCT(--ISNUMBER(--MID(A1,{6,7,8,9},1)))=4)

    Replace A1 with the cell in which you will enter PAN.

    Then, in the cell(in which you are going to enter pan), use data validation using custom and in formula section, enter reference to the cell in which Formula was put(=E1 in my case). You can also add error title and message in data validation in case you want.

    To automatically set PAN in UPPER CASE, you need to save the file as Enabled Macro file. For those who are not Familier with this, they need to enable Developer option from File>Options>Customize Ribbon, and at Right Side, Check Developer Option. Then you have to right click on the sheet and select view code. You need to enter following code in that

    Please Login or Register  to view this content.

    Select different range in case you want multiple cells to be UPPER CASE. This formula cannot compulsorily allow the 4th character to "F""I""C""H".

    Please Add Reputation if youl ike my work.
    Attached Files Attached Files
    Last edited by spymatemate; 07-28-2017 at 10:09 AM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Return Account Nickname if Account Number Matches
    By spacle in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-03-2013, 11:04 AM
  2. [SOLVED] How to censor account numbers based on how many characters are in the account number
    By Mcorydon in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-25-2013, 01:06 PM
  3. [SOLVED] need Macro code for validation of account number
    By tmprao27 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-17-2012, 06:30 AM
  4. Account number
    By Elle in forum Excel General
    Replies: 3
    Last Post: 01-06-2009, 10:19 AM
  5. sum by account number
    By drecollet in forum Excel General
    Replies: 2
    Last Post: 10-12-2007, 02:03 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1