Results 1 to 4 of 4

Thread: Validating PAN NUMBER Indian Format

  1. #1
    Junior Member
    Join Date
    Mar 2015
    Posts
    2
    Rep Power
    0

    Validating PAN NUMBER Indian Format

    Hi,

    How to use Data Validation on PAN NUMBER,

    The cell should take only if it is in such format.

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Junior Member
    Join Date
    Mar 2015
    Posts
    2
    Rep Power
    0

    But it gives an error.

    Quote Originally Posted by Admin View Post
    Hi,

    if i use the formula that u have suggested shows an error like " You may not use unions, intersections, or array constants for data validation criteria "

    What is the solution.

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    If the PAN is in A2, enter this formula in B2

    =AND(COUNT(MID(A2,{6,7,8,9},1)+0)=4,COUNT(SEARCH(" ~"&MID(A2,{1,2,3,4,5,10},{1,1,1,1,1,999}),"ABCDEFG HIJKLMNOPQRSTUVWXYZ"))=6)

    and in A2, use the DV formula as =B2=TRUE
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. Validating PAN (Indian Format)
    By Admin in forum Test Area
    Replies: 30
    Last Post: 03-22-2023, 06:14 PM
  2. Excel Number Format: Indian Style Comma Separation
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 6
    Last Post: 09-18-2013, 11:38 AM
  3. Replies: 3
    Last Post: 04-05-2013, 08:24 AM
  4. Validating 2 Columns using excel VBA
    By freakszzy in forum Excel Help
    Replies: 2
    Last Post: 07-26-2012, 12:46 PM
  5. Follow-up to "Excel Number Format: Indian Style Comma Separation"
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 2
    Last Post: 04-14-2012, 10:46 PM

Posting Permissions

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