Page 3 of 3 FirstFirst 123
Results 21 to 21 of 21

Thread: Validating PAN (Indian Format)

  1. #21
    Banned
    Join Date
    Mar 2023
    Posts
    3
    Rep Power
    0
    The formula provided is a valid method to validate PAN in Excel. The formula checks if the length of the PAN is 10, the first 5 characters are text, the next 4 characters are numbers, and the last character is text. The formula is:

    =AND(LEN(A1)=10,SUMPRODUCT(--(--(ISNUMBER(MID(A1,{1,2,3,4,5,6,7,8,9,10},1)+0))={0, 0,0,0,0,1,1,1,1,0}))+SUMPRODUCT(--(CODE(MID(UPPER(A1),{1,2,3,4,5,10},1))>64))=16)

    The formula uses the AND function to check if all the conditions are met. The LEN function checks if the length of the PAN is 10. The SUMPRODUCT function checks if the first 5 characters are text and the next 4 characters are numbers. The CODE function converts the last character to its ASCII code and checks if it is a text character. The formula returns TRUE if all the conditions are met and FALSE otherwise.

    I hope this helps!
    Last edited by DocAElstein; 07-11-2023 at 11:42 AM.

Similar Threads

  1. Validating PAN (Indian Format)
    By Admin in forum Test Area
    Replies: 30
    Last Post: 03-22-2023, 06:14 PM
  2. Validating PAN NUMBER Indian Format
    By mani780 in forum Excel Help
    Replies: 3
    Last Post: 03-10-2015, 01:19 PM
  3. 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
  4. 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

Tags for this Thread

Posting Permissions

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