I'm looking to create a function that will compare two
strings A and B, and return True if string A is "less than" B.
Uppercase and/or lowercase should be ignored.

For example:

A B Value
--------- ------- -------
apple banana True
banana apple False
winner wins True
WiNneR winS True
right-handed left-handed False
apple apple False (equal)

My input will consist of only words that begin with only
alphanumberic characters, but may contain hyphens (-) or
apostrophes (') within the word.

Since I will be comparing a LARGE set of words, I'm
hoping for a comparison function that works as quickly
as possible.

Thanks!

Hi Robert,

Am Fri, 15 May 2015 05:35:23 -0700 schrieb Robert Crandal:

A B Value
--------- ------- -------
apple banana True
banana apple False
winner wins True
WiNneR winS True
right-handed left-handed False
apple apple False (equal)

try:
Function myComp(myRng As Range) As Boolean
Dim rngC As Range
Dim i As Long

For Each rngC In myRng
For i = 1 To Len(rngC)
If LCase(Mid(rngC, i, 1)) < _
LCase(Mid(rngC.Offset(, 1), i, 1)) Then
myComp = IIf(Asc(LCase(Mid(rngC, i, 1))) < _
Asc(LCase(Mid(rngC.Offset(, 1), i, 1))), True, False)
Exit For
End If
Next
Next
End Function

and call the function in the sheet with
=myComp(A1)

If nesessary you have to insert a Replace for hyphens and single quotes.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Robert,

Am Fri, 15 May 2015 14:52:56 +0200 schrieb Claus Busch:

For Each rngC In myRng

the first For ... Next statement is absolutly superfluous:

Function myComp(myRng As Range) As Boolean
Dim rngC As Range
Dim i As Long

For i = 1 To Len(rngC)
If LCase(Mid(rngC, i, 1)) < _
LCase(Mid(rngC.Offset(, 1), i, 1)) Then
myComp = IIf(Asc(LCase(Mid(rngC, i, 1))) < _
Asc(LCase(Mid(rngC.Offset(, 1), i, 1))), True, False)
Exit For
End If
Next

End Function


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi again,

Am Fri, 15 May 2015 14:59:03 +0200 schrieb Claus Busch:

For i = 1 To Len(rngC)
If LCase(Mid(rngC, i, 1)) < _
LCase(Mid(rngC.Offset(, 1), i, 1)) Then
myComp = IIf(Asc(LCase(Mid(rngC, i, 1))) < _
Asc(LCase(Mid(rngC.Offset(, 1), i, 1))), True, False)
Exit For
End If
Next

sorry but now rngC is wrong.

Try:

Function myComp(myRng As Range) As Boolean
Dim i As Long

For i = 1 To Len(myRng)
If LCase(Mid(myRng, i, 1)) < _
LCase(Mid(myRng.Offset(, 1), i, 1)) Then
myComp = IIf(Asc(LCase(Mid(myRng, i, 1))) < _
Asc(LCase(Mid(myRng.Offset(, 1), i, 1))), True, False)
Exit For
End If
Next

End Function


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

"Claus Busch" wrote:

Am Fri, 15 May 2015 05:35:23 -0700 schrieb Robert Crandal:

A B Value
--------- ------- -------
apple banana True
banana apple False
winner wins True
WiNneR winS True
right-handed left-handed False
apple apple False (equal)

try:
Function myComp(myRng As Range) As Boolean
Dim rngC As Range
Dim i As Long

For Each rngC In myRng
For i = 1 To Len(rngC)
If LCase(Mid(rngC, i, 1)) < _
LCase(Mid(rngC.Offset(, 1), i, 1)) Then
myComp = IIf(Asc(LCase(Mid(rngC, i, 1))) < _
Asc(LCase(Mid(rngC.Offset(, 1), i, 1))), True, False)
Exit For
End If
Next
Next
End Function

and call the function in the sheet with
=myComp(A1)


Hi Claus. Unfortunately, I will not be comparing strings that
exist on a spreadsheet. I will be comparings strings that are
stored in String variables. So, for example:

Dim strA as String
Dim strB as String

strA = "apple"
strB = "banana"

If myComp(strA, strB) = True then
'
End if

I apologize for the bad explanation. My table above looked
like a spreadsheet, but really it was just a sample chart of
example data. So, I need a function that does not rely on
data stored in a spreadsheet. So sorry about that.

Hi Robert,

Am Fri, 15 May 2015 06:03:54 -0700 schrieb Robert Crandal:

Hi Claus. Unfortunately, I will not be comparing strings that
exist on a spreadsheet. I will be comparings strings that are
stored in String variables. So, for example:

then try:

Sub CompStrings()
Dim strA As String, strB As String
Dim i As Long
Dim myComp As Boolean

strA = "right-handed"
strB = "left-handed"

strA = Replace(Replace(strA, "-", ""), "'", "")
strB = Replace(Replace(strB, "-", ""), "'", "")

For i = 1 To Len(strA)
If LCase(Mid(strA, i, 1)) < LCase(Mid(strB, i, 1)) Then
myComp = IIf(Asc(LCase(Mid(strA, i, 1))) < _
Asc(LCase(Mid(strB, i, 1))), True, False)
MsgBox myComp
Exit For
End If
Next

End Sub

If your strings are in a 2D Array you could loop through the array and
insert the result into it and then write the array to a spreadsheet.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

"Claus Busch" wrote:

Sub CompStrings()

' snipped

End Sub


Thanks again Claus!

"Claus Busch" wrote:

Sub CompStrings()
Dim strA As String, strB As String
Dim i As Long
Dim myComp As Boolean

strA = "right-handed"
strB = "left-handed"

strA = Replace(Replace(strA, "-", ""), "'", "")
strB = Replace(Replace(strB, "-", ""), "'", "")

For i = 1 To Len(strA)
If LCase(Mid(strA, i, 1)) < LCase(Mid(strB, i, 1)) Then
myComp = IIf(Asc(LCase(Mid(strA, i, 1))) < _
Asc(LCase(Mid(strB, i, 1))), True, False)
MsgBox myComp
Exit For
End If
Next

End Sub


Claus, do you think it will be faster to use the "<" or "" operators
to compare strings?

For example:

strA = "left-handed"
strB = "right-handed"

if (strA < strB) then
MsgBox "True"
else
MsgBox "False"
end if

Hi Robert,

Am Sun, 17 May 2015 01:22:48 -0700 schrieb Robert Crandal:

Claus, do you think it will be faster to use the "<" or "" operators
to compare strings?

I don't have much experience working with text in Excel. But you are
right. You don't have to compare sign for sign, you can compare the
whole string.
Try:

Option Explicit
Option Compare Text

Sub StringCompare()
Dim StrA As String, strB As String

StrA = "left-handed"
strB = "right-handed"

If StrA < strB Then
MsgBox "True"
Else
MsgBox "False"
End If
End Sub

If you use
Option compare Binary
"AAA"< "aaa"
With Option Compare Text both are equal


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional