Validating PAN (Indian Format)

**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

**Admin** · 10-17-2012, 01:52 PM

Hi

Thanks for posting the code. Since this particular forum is dedicated to tips and tricks, please do ask question in Excel Help

**in.vaibhav** · 10-17-2012, 03:01 PM

Thanks. Rilli good piece of work..:cool :

Once again thanks a lot!!!

Originally Posted by Rick Rothstein

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

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

Ok, I agreed Sir.

Rgds

Originally Posted by Admin

Hi

Thanks for posting the code. Since this particular forum is dedicated to tips and tricks, please do ask question in Excel Help

**udaysdevadiga@gmail.com** · 12-27-2012, 08:52 PM

Hello Sir,

The above formula works fine.

Thanks for the same. Would request you to let us know how to save the same in excel so that every time no need of pasting the above formula again ana again.

Regards,
Uday

**Rick Rothstein** · 12-27-2012, 09:32 PM

Originally Posted by udaysdevadiga@gmail.com

The above formula works fine.

Thanks for the same. Would request you to let us know how to save the same in excel so that every time no need of pasting the above formula again ana again.

Do you really mean "formula" or are you referring to the VB code? There are 15 prior messages in this thread, some with formulas and some with VB code solutions... what is the number for the message containing the "formula" you are referring to (look for the number on the right side of the title bar for the message)?

**udaysdevadiga@gmail.com** · 12-27-2012, 09:40 PM

Hello Sir,

I am talking about the VB code. which is as below given bu you.

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

**Rick Rothstein** · 12-27-2012, 09:50 PM

Originally Posted by udaysdevadiga@gmail.com

Hello Sir,

I am talking about the VB code. which is as below given bu you.

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

Okay, I think the best way to do what I think you want is to create a template. Start a new workbook and put the above code in a module in that new workbook, then use Save As to save the workbook as an Excel Macro Enabled Template (give it a name you will recognize). Now, the next time you start a workbook that needs the PAN functionality, select that template instead of a blank workbook and the function will be in place ready-to-use.

**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")