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

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://eileenslounge.com/viewtopic.php?p=317218#p317218
    https://eileenslounge.com/viewtopic.php?p=316955#p316955
    https://eileenslounge.com/viewtopic.php?p=316955#p316955
    https://eileenslounge.com/viewtopic.php?p=316940#p316940
    https://eileenslounge.com/viewtopic.php?p=316927#p316927
    https://eileenslounge.com/viewtopic.php?p=317014#p317014
    https://eileenslounge.com/viewtopic.php?p=317006#p317006
    https://eileenslounge.com/viewtopic.php?p=316935#p316935
    https://eileenslounge.com/viewtopic.php?p=316875#p316875
    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-20-2024 at 04:14 PM.
    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)

  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/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg
    https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg.9irLgSdeU3r9itU7zdnW Hw
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9htG01cKBzX
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htJ6TpIO XR
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htOKs4jh 3M
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 10-24-2023 at 02:43 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)
    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)

  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)

  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

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

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
  •