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?

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" <[email protected]> wrote in message
news:[email protected]...
> 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?

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?

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?

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?

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?

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?

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?

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" <[email protected]> wrote in message
news:[email protected]...
> 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?

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?