Results 1 to 10 of 31

Thread: Validating PAN (Indian Format)

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #10
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    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


    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgxsozCmRd3RAmIPO5B4AaABAg.9fxrOrrvTln9g9wr8mv2 CS
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg.9fz3_oaiUeK9g96yGbAX 4t
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9g7pczEpcTz
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg.9fz3_oaiUeK9g7lhoX-ar5
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gD0AA-sfpl
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gECpsAVGbh
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg.9g9wJCunNRa9gJGhDZ4R I2
    https://www.youtube.com/watch?v=Sh1kZD7EVj0&lc=Ugz-pow-E8FDG8gFZ4l4AaABAg.9f8Bng22e5d9f8hoJGZY-5
    https://www.youtube.com/watch?v=Sh1kZD7EVj0&lc=Ugxev2gQt7BKZ0WYMfh4AaABAg.9f6hAjkC0ct9f8jleOui-u
    https://www.youtube.com/watch?v=Sh1kZD7EVj0&lc=Ugxg9iT7MPWGBWruIzR4AaABAg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA



    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=tzbKqTRuRzU&lc=UgyYW2WZ2DvSrzUKnJ14AaABAg
    https://www.youtube.com/watch?v=UywjKEMjSp0&lc=UgxIySxHPqM1RxtVqoR4AaABAg.9edGvmwOLq99eekDyfS0 CD
    https://www.youtube.com/watch?v=UywjKEMjSp0&lc=UgxIySxHPqM1RxtVqoR4AaABAg.9edGvmwOLq99eevG7txd 2c
    https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg
    https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgzytUUVRyw9U55-6M54AaABAg
    https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgzCoa6tOVIBxRDDDbN4AaABAg
    https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgyriWOelbVnw4FHWT54AaABAg.9dPo-OdLmZ09dc21kigjmr
    https://www.youtube.com/watch?v=363wd2EtQZ0&lc=UgzDQfo5rJqyVwvv2r54AaABAg
    https://www.youtube.com/watch?v=363wd2EtQZ0&lc=UgzHTSka7YppBdmUooV4AaABAg.9cXui6zzkz09cZttH_-2Gf
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 07-13-2023 at 10:34 PM.

Similar Threads

  1. 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
  2. Replies: 3
    Last Post: 04-05-2013, 08:24 AM
  3. Validating 2 Columns using excel VBA
    By freakszzy in forum Excel Help
    Replies: 2
    Last Post: 07-26-2012, 12:46 PM
  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
  5. Convert Text In YYYYMMDD Format To Date Format
    By S M C in forum Excel and VBA Tips and Tricks
    Replies: 1
    Last Post: 02-28-2012, 12:04 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
  •