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
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
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)?Originally Posted by udaysdevadiga@gmail.com
@ 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)
Last edited by DocAElstein; 07-11-2023 at 11:40 AM.
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)
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)
Last edited by DocAElstein; 07-11-2023 at 11:40 AM.
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)
Last edited by DocAElstein; 07-11-2023 at 11:41 AM.
Hi
Thanks for posting the code. Since this particular forum is dedicated to tips and tricks, please do ask question in Excel Help
Last edited by DocAElstein; 07-11-2023 at 11:41 AM.
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)
Ok, I agreed Sir.
Rgds
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.
The formula provided is a valid method to validate PAN in Excel. The formula checks if the length of the PAN is 10, the first 5 characters are text, the next 4 characters are numbers, and the last character is text. The formula is:
=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)
The formula uses the AND function to check if all the conditions are met. The LEN function checks if the length of the PAN is 10. The SUMPRODUCT function checks if the first 5 characters are text and the next 4 characters are numbers. The CODE function converts the last character to its ASCII code and checks if it is a text character. The formula returns TRUE if all the conditions are met and FALSE otherwise.
I hope this helps!
Last edited by DocAElstein; 07-11-2023 at 11:42 AM.
Posting Permissions
Reply With Quote
Bookmarks