Validating PAN (Indian Format)

**Admin** · 05-14-2012, 04:01 PM

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

**Rick Rothstein** · 05-14-2012, 08:07 PM

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

**Rick Rothstein** · 05-14-2012, 09:05 PM

**in.vaibhav** · 10-16-2012, 06:45 PM

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

**Chidambaram3983** · 09-02-2021, 08:34 AM

Here is the Solution...

=IF(AND(OR(MID(A1,4,1)={"P";"H";"B";"C";"L";"F";"A";"J";"G";"T"}),CODE(UPPER(MID(A1,{1,2,3,4,5},1)))>=65,CODE(UPPER(MID(A1,{1,2,3,4,5},1)))<=90,CODE(LOWER(MID(A1,{10},1)))>=97,CODE(LOWER(MID(A1,{10},1)))<=122,CODE(MID(A1,{6,7,8,9},1))>=49,CODE(MID(A1,{6,7,8,9},1))<=57,LEN(A1)=10),"VALID","INVALID")

**Chidambaram3983** · 09-02-2021, 08:38 AM

Originally Posted by in.vaibhav

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

Try This...

=IF(AND(OR(MID(A1,4,1)={"P";"H";"B";"C";"L";"F";"A";"J";"G";"T"}),CODE(UPPER(MID(A1,{1,2,3,4,5},1)))>=65,CODE(UPPER(MID(A1,{1,2,3,4,5},1)))<=90,CODE(LOWER(MID(A1,{10},1)))>=97,CODE(LOWER(MID(A1,{10},1)))<=122,CODE(MID(A1,{6,7,8,9},1))>=49,CODE(MID(A1,{6,7,8,9},1))<=57,LEN(A1)=10),"VALID","INVALID")

**in.vaibhav** · 10-16-2012, 07:52 PM

Originally Posted by Rick Rothstein

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

**Rick Rothstein** · 10-17-2012, 05:16 AM

Originally Posted by in.vaibhav

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

**in.vaibhav** · 10-17-2012, 12:38 PM

Originally Posted by Rick Rothstein

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

**Rick Rothstein** · 10-17-2012, 01:48 PM

I don't know this is a lot shorter than you were hoping for, but given you wanted complete error reporting, looping does not really help that much. I noticed you only tested the 4th character to be the letter "P" in your code and, since you said your code works fine, I restricted the check of the 4th character to only that letter. Note that I also added a check to see if the argument is too short or too long to be a proper entry. Here is the non-looping code I came up with...

Code:

Function IsPAN(S As String) As String
  If Len(S) < 10 Then
    IsPAN = "Too short!"
  ElseIf Len(S) > 10 Then
    IsPAN = "Too long!"
  Else
    If Mid(S, 1, 1) Like "[!A-Za-z]" Then IsPAN = IsPAN & vbLf & "Character 1 is not text."
    If Mid(S, 2, 1) Like "[!A-Za-z]" Then IsPAN = IsPAN & vbLf & "Character 2 is not text."
    If Mid(S, 3, 1) Like "[!A-Za-z]" Then IsPAN = IsPAN & vbLf & "Character 3 is not text."
    If Mid(S, 4, 1) Like "[!Pp]" Then IsPAN = IsPAN & vbLf & "Character 4 is not the letter 'P'."
    If Mid(S, 5, 1) Like "[!A-Za-z]" Then IsPAN = IsPAN & vbLf & "Character 5 is not text."
    If Mid(S, 6, 1) Like "[!0-9]" Then IsPAN = IsPAN & vbLf & "Character 6 is not a number."
    If Mid(S, 7, 1) Like "[!0-9]" Then IsPAN = IsPAN & vbLf & "Character 7 is not a number."
    If Mid(S, 8, 1) Like "[!0-9]" Then IsPAN = IsPAN & vbLf & "Character 8 is not a number."
    If Mid(S, 9, 1) Like "[!0-9]" Then IsPAN = IsPAN & vbLf & "Character 9 is not a number."
    If Mid(S, 10, 1) Like "[!A-Za-z]" Then IsPAN = IsPAN & vbLf & "Character 10 is not text."
    If Len(IsPAN) Then
      IsPAN = Mid(IsPAN, 2)
    Else
      IsPAN = "OK"
    End If
  End If
End Function

In case you are interested, here is the same function using loops (among other things)...

Code:

Function IsPAN(S As String) As String
  Dim X As Long, PatternChar As String, Test As New Collection
  Const Pattern As String = "AAAPANNNNA"
  If Len(S) < 10 Then
    IsPAN = "Too short!"
  ElseIf Len(S) > 10 Then
    IsPAN = "Too long!"
  ElseIf Len(IsPAN) = 0 Then
    Test.Add "[A-Za-z]", "A"
    Test.Add "[Pp]", "P"
    Test.Add "#", "N"
    For X = 1 To Len(S)
      PatternChar = Test(Mid(Pattern, X, 1))
      If Not Mid(S, X, 1) Like PatternChar Then
        IsPAN = IsPAN & vbLf & "Character " & X & " is not " & Choose(InStr("APN", _
                Mid(Pattern, X, 1)), "text.", "the letter ""P"".", "a number.")
      End If
    Next
    If Len(IsPAN) Then
      IsPAN = Mid(IsPAN, 2)
    Else
      IsPAN = "OK"
    End If
  End If
End Function