Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
String Comparison
Hi...
I want to perform a comparison on two string expressions. The strings would normally be of the form "ABC Systems Limited" or "ABC Systems LTD". Now if I were to compare these two strings, I want the macro to return true even if the last part of the strings dont match exactly, i.e. Limited and LTD. In simpler words, it should return true for two strings expressions containing abbreviations like LTD( Limited), Hlgds( Holdings) etc. Can anyone please help me with this? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
String Comparison
do you have a ddefined list of what words are equivalent?
If so you could first use (eg) sText=Replace(sText, "LTD", "Limited") sText=Replace(sText, "Hlgds", "Holdings") sText=Replace(sText, "etc", "etcetera") before doing the comparison. Tim. "Purnima" wrote in message ... Hi... I want to perform a comparison on two string expressions. The strings would normally be of the form "ABC Systems Limited" or "ABC Systems LTD". Now if I were to compare these two strings, I want the macro to return true even if the last part of the strings dont match exactly, i.e. Limited and LTD. In simpler words, it should return true for two strings expressions containing abbreviations like LTD( Limited), Hlgds( Holdings) etc. Can anyone please help me with this? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
String Comparison
You might consider dropping the last word from each string before comparing
them: Function Compare(ByVal String1 As String, ByVal String2 As String) As Boolean Compare = UCase(Left(String1, InStrRev(String1, " "))) = UCase(Left(String2, InStrRev(String2, " "))) End Function Sub xx() Debug.Print Compare("ABC SYSTEMS LTD", "abc Systems limited") End Sub "Purnima" wrote: Hi... I want to perform a comparison on two string expressions. The strings would normally be of the form "ABC Systems Limited" or "ABC Systems LTD". Now if I were to compare these two strings, I want the macro to return true even if the last part of the strings dont match exactly, i.e. Limited and LTD. In simpler words, it should return true for two strings expressions containing abbreviations like LTD( Limited), Hlgds( Holdings) etc. Can anyone please help me with this? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
String Comparison
Hi..
I cannot drop the last word from the comparison. I wish to compare the whole string including the last words of both string expressions. The result should be as follows: String1 String2 Result 1. ABC Systems Limited ABC Systems Ltd TRUE 2. ABC Systems Limited ABC Systems Inc. FALSE So I need to check whether the last word in one of the string expr. is an abbreviation of the last word in the other string expr. If it is, then return true, but if it is a totally different abbr. then return false. "AA2e72E" wrote: You might consider dropping the last word from each string before comparing them: Function Compare(ByVal String1 As String, ByVal String2 As String) As Boolean Compare = UCase(Left(String1, InStrRev(String1, " "))) = UCase(Left(String2, InStrRev(String2, " "))) End Function Sub xx() Debug.Print Compare("ABC SYSTEMS LTD", "abc Systems limited") End Sub "Purnima" wrote: Hi... I want to perform a comparison on two string expressions. The strings would normally be of the form "ABC Systems Limited" or "ABC Systems LTD". Now if I were to compare these two strings, I want the macro to return true even if the last part of the strings dont match exactly, i.e. Limited and LTD. In simpler words, it should return true for two strings expressions containing abbreviations like LTD( Limited), Hlgds( Holdings) etc. Can anyone please help me with this? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
String Comparison
Can you restrict the comparison by using just the first letter of the last
word? If , yes, try Function Compare(ByVal String1 As String, ByVal String2 As String) As Boolean Compare = UCase(Left(String1, 1 + InStrRev(String1, " "))) = UCase(Left(String2, 1 + InStrRev(String2, " "))) End Function Sub xx() Debug.Print Compare("ABC SYSTEMS LTD", "abc Systems Inc") End Sub "Purnima" wrote: Hi.. I cannot drop the last word from the comparison. I wish to compare the whole string including the last words of both string expressions. The result should be as follows: String1 String2 Result 1. ABC Systems Limited ABC Systems Ltd TRUE 2. ABC Systems Limited ABC Systems Inc. FALSE So I need to check whether the last word in one of the string expr. is an abbreviation of the last word in the other string expr. If it is, then return true, but if it is a totally different abbr. then return false. "AA2e72E" wrote: You might consider dropping the last word from each string before comparing them: Function Compare(ByVal String1 As String, ByVal String2 As String) As Boolean Compare = UCase(Left(String1, InStrRev(String1, " "))) = UCase(Left(String2, InStrRev(String2, " "))) End Function Sub xx() Debug.Print Compare("ABC SYSTEMS LTD", "abc Systems limited") End Sub "Purnima" wrote: Hi... I want to perform a comparison on two string expressions. The strings would normally be of the form "ABC Systems Limited" or "ABC Systems LTD". Now if I were to compare these two strings, I want the macro to return true even if the last part of the strings dont match exactly, i.e. Limited and LTD. In simpler words, it should return true for two strings expressions containing abbreviations like LTD( Limited), Hlgds( Holdings) etc. Can anyone please help me with this? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
String Comparison
Hi..
Thanx for the help. Is there a way by which I could put each string expression in an array and then compare them word by word. If all the words match except the last one, then we could check if it is an abbreviated form of the last word in the other string expression. Otherwise if all words match ,then that obviously means that both string expressions are the same. "AA2e72E" wrote: Can you restrict the comparison by using just the first letter of the last word? If , yes, try Function Compare(ByVal String1 As String, ByVal String2 As String) As Boolean Compare = UCase(Left(String1, 1 + InStrRev(String1, " "))) = UCase(Left(String2, 1 + InStrRev(String2, " "))) End Function Sub xx() Debug.Print Compare("ABC SYSTEMS LTD", "abc Systems Inc") End Sub "Purnima" wrote: Hi.. I cannot drop the last word from the comparison. I wish to compare the whole string including the last words of both string expressions. The result should be as follows: String1 String2 Result 1. ABC Systems Limited ABC Systems Ltd TRUE 2. ABC Systems Limited ABC Systems Inc. FALSE So I need to check whether the last word in one of the string expr. is an abbreviation of the last word in the other string expr. If it is, then return true, but if it is a totally different abbr. then return false. "AA2e72E" wrote: You might consider dropping the last word from each string before comparing them: Function Compare(ByVal String1 As String, ByVal String2 As String) As Boolean Compare = UCase(Left(String1, InStrRev(String1, " "))) = UCase(Left(String2, InStrRev(String2, " "))) End Function Sub xx() Debug.Print Compare("ABC SYSTEMS LTD", "abc Systems limited") End Sub "Purnima" wrote: Hi... I want to perform a comparison on two string expressions. The strings would normally be of the form "ABC Systems Limited" or "ABC Systems LTD". Now if I were to compare these two strings, I want the macro to return true even if the last part of the strings dont match exactly, i.e. Limited and LTD. In simpler words, it should return true for two strings expressions containing abbreviations like LTD( Limited), Hlgds( Holdings) etc. Can anyone please help me with this? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
String Comparison
Try the code below:
1. if string1 & string2 match, return true 2. if string1 without last word matches string2 without last word, then "Purnima" wrote: Hi.. Thanx for the help. Is there a way by which I could put each string expression in an array and then compare them word by word. If all the words match except the last one, then we could check if it is an abbreviated form of the last word in the other string expression. Otherwise if all words match ,then that obviously means that both string expressions are the same. "AA2e72E" wrote: Can you restrict the comparison by using just the first letter of the last word? If , yes, try Function Compare(ByVal String1 As String, ByVal String2 As String) As Boolean Compare = UCase(Left(String1, 1 + InStrRev(String1, " "))) = UCase(Left(String2, 1 + InStrRev(String2, " "))) End Function Sub xx() Debug.Print Compare("ABC SYSTEMS LTD", "abc Systems Inc") End Sub "Purnima" wrote: Hi.. I cannot drop the last word from the comparison. I wish to compare the whole string including the last words of both string expressions. The result should be as follows: String1 String2 Result 1. ABC Systems Limited ABC Systems Ltd TRUE 2. ABC Systems Limited ABC Systems Inc. FALSE So I need to check whether the last word in one of the string expr. is an abbreviation of the last word in the other string expr. If it is, then return true, but if it is a totally different abbr. then return false. "AA2e72E" wrote: You might consider dropping the last word from each string before comparing them: Function Compare(ByVal String1 As String, ByVal String2 As String) As Boolean Compare = UCase(Left(String1, InStrRev(String1, " "))) = UCase(Left(String2, InStrRev(String2, " "))) End Function Sub xx() Debug.Print Compare("ABC SYSTEMS LTD", "abc Systems limited") End Sub "Purnima" wrote: Hi... I want to perform a comparison on two string expressions. The strings would normally be of the form "ABC Systems Limited" or "ABC Systems LTD". Now if I were to compare these two strings, I want the macro to return true even if the last part of the strings dont match exactly, i.e. Limited and LTD. In simpler words, it should return true for two strings expressions containing abbreviations like LTD( Limited), Hlgds( Holdings) etc. Can anyone please help me with this? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
String Comparison
try (test) the code below:
1. if string1 and string2 match, return true else false 2. if string1 withut last word matches string2 without last word then: if last word of string1 is shorter than last word of string2, return true if ALL letters of last word of string1 is found in last word of string2 else false if last word of string1 is longer than last word of string2, return true if ALL letters of last word of string2 is found in last word of string1 else false IAM ASSUMING THAT ANY ABBREVIATION IS A SUBSET OF THE LETTERS OF THE FULL WORD. Function Compare(ByVal String1 As String, ByVal String2 As String) As Boolean String1 = UCase(String1) String2 = UCase(String2) If String1 = String2 Then Compare = True Else If Left(String1, InStrRev(String1, " ")) = Left(String2, InStrRev(String2, " ")) Then String1 = Mid(String1, 1 + InStrRev(String1, " ")) String2 = Mid(String2, 1 + InStrRev(String2, " ")) Compare = True If Len(String1) < Len(String2) Then For i = 1 To Len(String1) Compare = Compare And 0 < InStr(String2, Mid(String1, i, 1)) If Not Compare Then Exit Function Next Else For i = 1 To Len(String2) Compare = Compare And 0 < InStr(String1, Mid(String2, i, 1)) If Not Compare Then Exit Function Next End If End If End If End Function Sub xx() Debug.Print Compare("ABC SYSTEMS LTD", "abc Systems Limited") Debug.Print Compare("ABC SYSTEMS LIMITED", "abc Systems Ltd") End Sub "Purnima" wrote: Hi.. Thanx for the help. Is there a way by which I could put each string expression in an array and then compare them word by word. If all the words match except the last one, then we could check if it is an abbreviated form of the last word in the other string expression. Otherwise if all words match ,then that obviously means that both string expressions are the same. "AA2e72E" wrote: Can you restrict the comparison by using just the first letter of the last word? If , yes, try Function Compare(ByVal String1 As String, ByVal String2 As String) As Boolean Compare = UCase(Left(String1, 1 + InStrRev(String1, " "))) = UCase(Left(String2, 1 + InStrRev(String2, " "))) End Function Sub xx() Debug.Print Compare("ABC SYSTEMS LTD", "abc Systems Inc") End Sub "Purnima" wrote: Hi.. I cannot drop the last word from the comparison. I wish to compare the whole string including the last words of both string expressions. The result should be as follows: String1 String2 Result 1. ABC Systems Limited ABC Systems Ltd TRUE 2. ABC Systems Limited ABC Systems Inc. FALSE So I need to check whether the last word in one of the string expr. is an abbreviation of the last word in the other string expr. If it is, then return true, but if it is a totally different abbr. then return false. "AA2e72E" wrote: You might consider dropping the last word from each string before comparing them: Function Compare(ByVal String1 As String, ByVal String2 As String) As Boolean Compare = UCase(Left(String1, InStrRev(String1, " "))) = UCase(Left(String2, InStrRev(String2, " "))) End Function Sub xx() Debug.Print Compare("ABC SYSTEMS LTD", "abc Systems limited") End Sub "Purnima" wrote: Hi... I want to perform a comparison on two string expressions. The strings would normally be of the form "ABC Systems Limited" or "ABC Systems LTD". Now if I were to compare these two strings, I want the macro to return true even if the last part of the strings dont match exactly, i.e. Limited and LTD. In simpler words, it should return true for two strings expressions containing abbreviations like LTD( Limited), Hlgds( Holdings) etc. Can anyone please help me with this? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
String Comparison
Wow.......I believe that would work......thanx a million!
Cheers, Purnima. "AA2e72E" wrote: try (test) the code below: 1. if string1 and string2 match, return true else false 2. if string1 withut last word matches string2 without last word then: if last word of string1 is shorter than last word of string2, return true if ALL letters of last word of string1 is found in last word of string2 else false if last word of string1 is longer than last word of string2, return true if ALL letters of last word of string2 is found in last word of string1 else false IAM ASSUMING THAT ANY ABBREVIATION IS A SUBSET OF THE LETTERS OF THE FULL WORD. Function Compare(ByVal String1 As String, ByVal String2 As String) As Boolean String1 = UCase(String1) String2 = UCase(String2) If String1 = String2 Then Compare = True Else If Left(String1, InStrRev(String1, " ")) = Left(String2, InStrRev(String2, " ")) Then String1 = Mid(String1, 1 + InStrRev(String1, " ")) String2 = Mid(String2, 1 + InStrRev(String2, " ")) Compare = True If Len(String1) < Len(String2) Then For i = 1 To Len(String1) Compare = Compare And 0 < InStr(String2, Mid(String1, i, 1)) If Not Compare Then Exit Function Next Else For i = 1 To Len(String2) Compare = Compare And 0 < InStr(String1, Mid(String2, i, 1)) If Not Compare Then Exit Function Next End If End If End If End Function Sub xx() Debug.Print Compare("ABC SYSTEMS LTD", "abc Systems Limited") Debug.Print Compare("ABC SYSTEMS LIMITED", "abc Systems Ltd") End Sub "Purnima" wrote: Hi.. Thanx for the help. Is there a way by which I could put each string expression in an array and then compare them word by word. If all the words match except the last one, then we could check if it is an abbreviated form of the last word in the other string expression. Otherwise if all words match ,then that obviously means that both string expressions are the same. "AA2e72E" wrote: Can you restrict the comparison by using just the first letter of the last word? If , yes, try Function Compare(ByVal String1 As String, ByVal String2 As String) As Boolean Compare = UCase(Left(String1, 1 + InStrRev(String1, " "))) = UCase(Left(String2, 1 + InStrRev(String2, " "))) End Function Sub xx() Debug.Print Compare("ABC SYSTEMS LTD", "abc Systems Inc") End Sub "Purnima" wrote: Hi.. I cannot drop the last word from the comparison. I wish to compare the whole string including the last words of both string expressions. The result should be as follows: String1 String2 Result 1. ABC Systems Limited ABC Systems Ltd TRUE 2. ABC Systems Limited ABC Systems Inc. FALSE So I need to check whether the last word in one of the string expr. is an abbreviation of the last word in the other string expr. If it is, then return true, but if it is a totally different abbr. then return false. "AA2e72E" wrote: You might consider dropping the last word from each string before comparing them: Function Compare(ByVal String1 As String, ByVal String2 As String) As Boolean Compare = UCase(Left(String1, InStrRev(String1, " "))) = UCase(Left(String2, InStrRev(String2, " "))) End Function Sub xx() Debug.Print Compare("ABC SYSTEMS LTD", "abc Systems limited") End Sub "Purnima" wrote: Hi... I want to perform a comparison on two string expressions. The strings would normally be of the form "ABC Systems Limited" or "ABC Systems LTD". Now if I were to compare these two strings, I want the macro to return true even if the last part of the strings dont match exactly, i.e. Limited and LTD. In simpler words, it should return true for two strings expressions containing abbreviations like LTD( Limited), Hlgds( Holdings) etc. Can anyone please help me with this? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
String Comparison
Purnima,
How about this function: Public Function MatchMe(argCompanyName As String) As String Dim CompanyType As String Dim arrCompanyTypes As Variant Dim i As Long Dim Temp As String Const CompanyTypes As String = "Company,Incorporated,Holdings,Limited" arrCompanyTypes = Split(CompanyTypes, ",") 'Get the last part of the string Temp = Mid(argCompanyName, InStrRev(argCompanyName, " ") + 1) 'Insert an asterisk between each character For i = 1 To Len(CompanyType) CompanyType = CompanyType & Mid(Temp , i, 1) & "*" Next 'Compare to all of the full versions of CompanyType For i = 0 To UBound(arrCompanyTypes) If arrCompanyTypes(i) Like CompanyType Then MatchMe = arrCompanyTypes(i) Exit Function End If Next MatchMe = "Not Found" End Function You would still have to handle punctuation, capitalisation and finally replacing the company type. RegExp may be worth looking at also. NickHK "Purnima" wrote in message ... Hi.. Thanx for the help. Is there a way by which I could put each string expression in an array and then compare them word by word. If all the words match except the last one, then we could check if it is an abbreviated form of the last word in the other string expression. Otherwise if all words match ,then that obviously means that both string expressions are the same. "AA2e72E" wrote: Can you restrict the comparison by using just the first letter of the last word? If , yes, try Function Compare(ByVal String1 As String, ByVal String2 As String) As Boolean Compare = UCase(Left(String1, 1 + InStrRev(String1, " "))) = UCase(Left(String2, 1 + InStrRev(String2, " "))) End Function Sub xx() Debug.Print Compare("ABC SYSTEMS LTD", "abc Systems Inc") End Sub "Purnima" wrote: Hi.. I cannot drop the last word from the comparison. I wish to compare the whole string including the last words of both string expressions. The result should be as follows: String1 String2 Result 1. ABC Systems Limited ABC Systems Ltd TRUE 2. ABC Systems Limited ABC Systems Inc. FALSE So I need to check whether the last word in one of the string expr. is an abbreviation of the last word in the other string expr. If it is, then return true, but if it is a totally different abbr. then return false. "AA2e72E" wrote: You might consider dropping the last word from each string before comparing them: Function Compare(ByVal String1 As String, ByVal String2 As String) As Boolean Compare = UCase(Left(String1, InStrRev(String1, " "))) = UCase(Left(String2, InStrRev(String2, " "))) End Function Sub xx() Debug.Print Compare("ABC SYSTEMS LTD", "abc Systems limited") End Sub "Purnima" wrote: Hi... I want to perform a comparison on two string expressions. The strings would normally be of the form "ABC Systems Limited" or "ABC Systems LTD". Now if I were to compare these two strings, I want the macro to return true even if the last part of the strings dont match exactly, i.e. Limited and LTD. In simpler words, it should return true for two strings expressions containing abbreviations like LTD( Limited), Hlgds( Holdings) etc. Can anyone please help me with this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparison of alphanumeric string | Excel Discussion (Misc queries) | |||
Text string comparison - closest match | Excel Discussion (Misc queries) | |||
String Comparison & Conditional Formatting | Excel Discussion (Misc queries) | |||
comparison string VBA excell | Excel Discussion (Misc queries) | |||
VB String Comparison In Excel | Excel Programming |