Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 31

Thread: Validating PAN (Indian Format)

  1. 12-27-2012, 09:32 PM #21
    Rick Rothstein
    Rick Rothstein is offline
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by udaysdevadiga@gmail.com View Post
    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)?
    Last edited by Rick Rothstein; 12-27-2012 at 09:34 PM.
    Reply With Quote Reply With Quote
  2. 12-27-2012, 09:32 PM #22
    Rick Rothstein
    Rick Rothstein is offline
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by udaysdevadiga@gmail.com View Post
    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)?
    Reply With Quote Reply With Quote
  3. 12-27-2012, 09:40 PM #23
    udaysdevadiga@gmail.com
    udaysdevadiga@gmail.com is offline
    Junior Member
    Join Date
    May 2012
    Posts
    8
    Rep Power
    0
    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
    Reply With Quote Reply With Quote
  4. 12-27-2012, 09:40 PM #24
    udaysdevadiga@gmail.com
    udaysdevadiga@gmail.com is offline
    Junior Member
    Join Date
    May 2012
    Posts
    8
    Rep Power
    0
    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
    Reply With Quote Reply With Quote
  5. 12-27-2012, 09:50 PM #25
    Rick Rothstein
    Rick Rothstein is offline
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by udaysdevadiga@gmail.com View Post
    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.
    Reply With Quote Reply With Quote
  6. 12-27-2012, 09:50 PM #26
    Rick Rothstein
    Rick Rothstein is offline
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by udaysdevadiga@gmail.com View Post
    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.
    Reply With Quote Reply With Quote
  7. 09-02-2021, 08:34 AM #27
    Chidambaram3983
    Chidambaram3983 is offline
    Junior Member
    Join Date
    Sep 2021
    Posts
    9
    Rep Power
    0
    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")
    Reply With Quote Reply With Quote
  8. 09-02-2021, 08:34 AM #28
    Chidambaram3983
    Chidambaram3983 is offline
    Junior Member
    Join Date
    Sep 2021
    Posts
    9
    Rep Power
    0
    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")
    Reply With Quote Reply With Quote
  9. 09-02-2021, 08:38 AM #29
    Chidambaram3983
    Chidambaram3983 is offline
    Junior Member
    Join Date
    Sep 2021
    Posts
    9
    Rep Power
    0
    Quote Originally Posted by in.vaibhav View Post
    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")
    Reply With Quote Reply With Quote
  10. 09-02-2021, 08:38 AM #30
    Chidambaram3983
    Chidambaram3983 is offline
    Junior Member
    Join Date
    Sep 2021
    Posts
    9
    Rep Power
    0
    Quote Originally Posted by in.vaibhav View Post
    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")
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

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. Comparing Numbers When One Is Text Format And The Other Is Number Numeric Format
    By Howardc in forum Excel Help
    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

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules