Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: Validating PAN (Indian Format)

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

    Validating PAN (Indian Format)

    Hi All,

    Here is a method to validate PAN in Excel. The rules for validation are as follows:


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


    and the formula will be

    =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)

    More solutions are welcome
    Last edited by DocAElstein; 07-11-2023 at 11:38 AM.
    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)

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    There is always a VB solution, a UDF (user defined function) in this case...

    Code:
    Function IsPAN(S As String) As Boolean
      IsPAN = S Like "[A-Za-z][A-Za-z][A-Za-z][A-Za-z][A-Za-z]####[A-Za-z]"
    End Function
    For those reading this who are unfamiliar with UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use IsPAN just like it was a built-in Excel function. For example,

    =IsPAN(A1)

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg
    https://www.youtube.com/watch?v=DVFFApHzYVk&lc=Ugyi578yhj9zShmhuPl4AaABAg
    https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgxvxlnuTRWiV6MUZB14AaABAg
    https://www.youtube.com/watch?v=_8i1fVEi5WY&lc=Ugz0ptwE5J-2CpX4Lzh4AaABAg
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxoHAw8RwR7VmyVBUt4AaABAg.9C-br0lEl8V9xI0_6pCaR9
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=Ugz5DDCMqmHLeEjUU8t4AaABAg.9bl7m03Onql9xI-ar3Z0ME
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxYnpd9leriPmc8rPd4AaABAg.9gdrYDocLIm9xI-2ZpVF-q
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgyjoPLjNeIAOMVH_u94AaABAg.9id_Q3FO8Lp9xHyeYSuv 1I
    https://www.reddit.com/r/windowsxp/comments/pexq9q/comment/k81ybvj/?utm_source=reddit&utm_medium=web2x&context=3
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M
    ttps://www.youtube.com/watch?v=LP9fz2DCMBE
    https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg
    https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg.9wdo_rWgxSH9wdpcYqrv p8
    ttps://www.youtube.com/watch?v=bFxnXH4-L1A
    https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxuODisjo6cvom7O-B4AaABAg.9w_AeS3JiK09wdi2XviwLG
    https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxBU39bTptFznDC1PJ4AaABAg
    ttps://www.youtube.com/watch?v=GqzeFYWjTxI
    https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgwJnJDJ5JT8hFvibt14AaABAg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 11-30-2023 at 02:29 PM.

  3. #3
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    I just noticed that your formula will "false match" these characters...

    [\]^_`{|}~

    as well as almost all the characters with ASCII (technically, ANSI) values above 126 if they appear anywhere within the first 5 or the 10th character positions. I would note that the UDF that I posted earlier works correctly for these characters.

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/@alanelston2330
    https://www.youtube.com/watch?v=yXaYszT11CA&lc=UgxEjo0Di9-9cnl8UnZ4AaABAg.9XYLEH1OwDIA35HNIei0z-
    https://eileenslounge.com/viewtopic.php?p=316154#p316154
    https://www.youtube.com/watch?v=TW3l7PkSPD4&lc=UgwAL_Jrv7yg7WWC8x14AaABAg
    https://teylyn.com/2017/03/21/dollarsigns/#comment-191
    https://eileenslounge.com/viewtopic.php?p=317050#p317050
    https://eileenslounge.com/viewtopic.php?f=27&t=40953&p=316854#p316854
    https://www.eileenslounge.com/viewtopic.php?v=27&t=40953&p=316875#p316875
    https://eileenslounge.com/viewtopic.php?p=316057#p316057
    https://eileenslounge.com/viewtopic.php?p=316705#p316705
    https://eileenslounge.com/viewtopic.php?p=316704#p316704
    https://eileenslounge.com/viewtopic.php?p=176255#p176255
    https://eileenslounge.com/viewtopic.php?f=27&t=40919&p=316597#p316597
    https://eileenslounge.com/viewtopic.php?p=316412#p316412
    https://eileenslounge.com/viewtopic.php?p=316254#p316254
    https://eileenslounge.com/viewtopic.php?p=316280#p316280
    https://eileenslounge.com/viewtopic.php?p=315915#p315915
    https://eileenslounge.com/viewtopic.php?p=315512#p315512
    https://eileenslounge.com/viewtopic.php?p=315744#p315744
    https://www.eileenslounge.com/viewtopic.php?p=315512#p315512
    https://eileenslounge.com/viewtopic.php?p=315680#p315680
    https://eileenslounge.com/viewtopic.php?p=315743#p315743
    https://www.eileenslounge.com/viewtopic.php?p=315326#p315326
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40752
    https://eileenslounge.com/viewtopic.php?p=314950#p314950
    https://www.eileenslounge.com/viewtopic.php?p=314940#p314940
    https://www.eileenslounge.com/viewtopic.php?p=314926#p314926
    https://www.eileenslounge.com/viewtopic.php?p=314920#p314920
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 05-16-2024 at 02:54 PM.

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    @ Rick,

    Thanks for checking all the possibilities.

    Here is the updated one.

    =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)*(CODE(MID(UPPER(A1),{1,2,3,4,5,10},1))<91))=16)
    Last edited by DocAElstein; 07-11-2023 at 11:40 AM.
    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)

  5. #5
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by Admin View Post
    Here is the updated one.

    =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)*(CODE(MID(UPPER(A1),{1,2,3,4,5,10},1))<91))=16)
    This slightly shorter formula (using two less function calls) also appears to work...

    =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)
    Last edited by DocAElstein; 07-11-2023 at 11:40 AM.

  6. #6
    Junior Member Haseeb A's Avatar
    Join Date
    Apr 2011
    Posts
    21
    Rep Power
    0
    Quote Originally Posted by Rick Rothstein View Post
    ...
    =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)
    Rick,

    As you know you can shorten 2nd SUMPRODUCT(--ISNUMBER with COUNT,

    COUNT(--MID(A1,{6,7,8,9},1))

    Another one with less functions,

    =AND(COUNT(MID(A1,{6,7,8,9},1)+0)=4,COUNT(FIND(MID (UPPER(A1),{1,2,3,4,5,10},{1,1,1,1,1,999}),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))=6)

    EDIT:

    Using by a tilde "~" could avoid UPPER function,

    =AND(COUNT(MID(A1,{6,7,8,9},1)+0)=4,COUNT(SEARCH("~"&MID(A1,{1,2,3,4,5,10},{1,1,1,1,1,999}),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))=6)
    Last edited by DocAElstein; 07-11-2023 at 11:41 AM.

  7. #7
    Junior Member
    Join Date
    Oct 2012
    Posts
    31
    Rep Power
    0
    Hi

    There is one more requirement, 4Th Character should be P for Individulas, C for Company & so on.

    So how can we check that & also whether we can give output with error instead of say True or False.

    EG. Length should be 10, First 5 character should be text, Next 6-9 character should be number, Last character should be text, 4th character should be either of P,H,F,A,T,B,L,J,G.

    Thanks & Regards


    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

  8. #8
    Junior Member
    Join Date
    Oct 2012
    Posts
    31
    Rep Power
    0
    Quote Originally Posted by Rick Rothstein View Post
    There is always a VB solution, a UDF (user defined function) in this case...

    Code:
    Function IsPAN(S As String) As Boolean
      IsPAN = S Like "[A-Za-z][A-Za-z][A-Za-z][A-Za-z][A-Za-z]####[A-Za-z]"
    End Function
    For those reading this who are unfamiliar with UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use IsPAN just like it was a built-in Excel function. For example,

    =IsPAN(A1)
    Hi

    There is one more requirement in this UDF, 4Th Character should be P for Individulas, C for Company & so on.

    So how can we check that & also whether we can give output with error instead of say True or False.

    EG. Length should be 10, First 5 character should be text, Next 6-9 character should be number, Last character should be text, 4th character should be either of P,H,F,A,T,B,L,J,G.

    Thanks & Regards
    Last edited by DocAElstein; 07-11-2023 at 11:41 AM.

  9. #9
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by in.vaibhav View Post
    Hi

    There is one more requirement in this UDF, 4Th Character should be P for Individulas, C for Company & so on.

    So how can we check that & also whether we can give output with error instead of say True or False.

    EG. Length should be 10, First 5 character should be text, Next 6-9 character should be number, Last character should be text, 4th character should be either of P,H,F,A,T,B,L,J,G.

    Thanks & Regards
    This should account for the requirement you posted about...
    Code:
    Function IsPAN(S As String) As Boolean
      IsPAN = S Like "[A-Za-z][A-Za-z][A-Za-z][ABFGHJLPTabfghjlpt][A-Za-z]####[A-Za-z]"
    End Function

  10. #10
    Junior Member
    Join Date
    Oct 2012
    Posts
    31
    Rep Power
    0
    Quote Originally Posted by Rick Rothstein View Post
    This should account for the requirement you posted about...
    Code:
    Function IsPAN(S As String) As Boolean
      IsPAN = S Like "[A-Za-z][A-Za-z][A-Za-z][ABFGHJLPTabfghjlpt][A-Za-z]####[A-Za-z]"
    End Function
    Hi

    I wanted to function like this..
    Code:
    Function ISPAN(inData As String)
    Dim i As Integer
    Dim Result As String
    
    If Len(inData) <> 10 Then
    ISPAN = "Total Character is " & Len(inData)
    Exit Function
    End If
    
    If Mid(UCase(inData), 1, 1) Like "[A-Z]" Then
    r1 = ""
    Else
    r1 = "Char1 is not Text,"
    End If
    
    If Mid(UCase(inData), 2, 1) Like "[A-Z]" Then
    r2 = ""
    Else
    r2 = "Char2 is not Text,"
    End If
    
    If Mid(UCase(inData), 3, 1) Like "[A-Z]" Then
    r3 = ""
    Else
    r3 = "Char3 is not Text,"
    End If
    
    If Mid(UCase(inData), 4, 1) Like "[P]" Then
    r4 = ""
    Else
    r4 = "Char4 Should be P"
    End If
    
    If Mid(UCase(inData), 5, 1) Like "[A-Z]" Then
    r5 = ""
    Else
    r5 = "Char5 is not Text,"
    End If
    
    If Mid(UCase(inData), 6, 1) Like "#" Then
    r6 = ""
    Else
    r6 = "Char6 is not Number,"
    End If
    
    If Mid(UCase(inData), 7, 1) Like "#" Then
    r7 = ""
    Else
    r7 = "Char7 is not Number,"
    End If
    
    If Mid(UCase(inData), 8, 1) Like "#" Then
    r8 = ""
    Else
    r8 = "Char8 is not Number,"
    End If
    
    If Mid(UCase(inData), 9, 1) Like "#" Then
    r9 = ""
    Else
    r9 = "Char9 is not Number,"
    End If
    
    If Mid(UCase(inData), 10, 1) Like "[A-Z]" Then
    r10 = ""
    Else
    r10 = "Char10 is not Text,"
    End If
    
    Result = r1 & r2 & r3 & r4 & r5 & r6 & r7 & r8 & r9 & r10
    
    If Right(Result, 1) = "," Then
        Result = Left(Result, Len(Result) - 1)
    End If
    
    If Result = "" Then
        ISPAN = "OK"
            Else
        ISPAN = Result
        End If
    End Function
    This works fine but it is very lengthy can we shortn same by looping, I am not expert in VBA

    Thanks for all support.


    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

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
  •