Solved: Read string variable while ignoring case sensitiveity

Hi all just wondering how this is done.

for example

If i want to get the last 5 letters from PineApPle and match it with 'apple' or 'APPLE' OR 'aPPlE'.

Is there a way?

cheers

Hi

you can use the strcomp function with vbBinaryCompare to see if the letters are the same regardless of case or vbTextCompare for a case match.

Example
[VBA]Sub mymatch()
Dim str1 As String
Dim str2 As String
str1 = "APPLE"
str2 = "aPPlE"
If StrComp(str1, str2, vbBinaryCompare) Then
MsgBox ("match found")
End If

End Sub[/VBA]

regards
qff

That's the wrong way round - vbTextCompare ignores case, vbBinaryCompare is case sensitive. Additionally, StrComp returns 0 if there is a match, so the code should be:
[vba]Sub mymatch()
Dim str1 As String
Dim str2 As String
str1 = "APPLE"
str2 = "appLe"
If StrComp(str1, str2, vbTextCompare) = 0 Then
MsgBox ("match found")
End If
End Sub
[/vba]

Rory

thanks for pointing out my error.

I should know better than to post with a hangover

regards
qff

:)

Thanks guys
That looks wierd , but works, I would have thought if a string comparison equals zero (False) it would not return a match.?

cheers

I would use

[vba]

Sub mymatch()
Dim str1 As String
Dim str2 As String
str1 = "Pineapple"
str2 = "Apple"
If str1 Like "*" & str Then
MsgBox ("match found")
End If
[/vba]

It makes a bit more sense when you think that StrComp can tell you whether string 1 is less than, equal to, or greater than string 2 (returning -1, 0, or 1)
I was only commenting on qff's code though - for what you want, xld's is more specific.

Quote:

---

Originally Posted by daniel_d_n_r

Hi all just wondering how this is done.

for example

If i want to get the last 5 letters from PineApPle and match it with 'apple' or 'APPLE' OR 'aPPlE'.

Is there a way?

cheers

---

to cater for all the case variations you've given, use Option Compare Text, e.g.

[vba]
Option Explicit

Option Compare Text

Sub mymatch()
If ActiveCell.Value Like "*apple*" Then MsgBox "match found"
End Sub
[/vba]

It strange that VBA doesn't have a function like UCASE or LCASE to ignore case sensitivity.

thanks all for the helpful assistance

cheers

It does if you want that

[vba]

Sub mymatch()
Dim str1 As String
Dim str2 As String
str1 = "Pineapple"
str2 = "Apple"
If LCase(str1) Like "*" & LCase(str) Then
MsgBox ("match found")
End If
[/vba]

Quote:

---

Originally Posted by xld

It does if you want that

[vba]

Sub mymatch()
Dim str1 As String
Dim str2 As String
str1 = "Pineapple"
str2 = "Apple"
If LCase(str1) Like "*" & LCase(str) Then
MsgBox ("match found")
End If
[/vba]

---

Exactly, but that is not ignoring case sensitivity, it's forcing changes to case to enable a comparison to be made. Option Compare Text is really the only way to do what you want i.e. to ignore case altogether...

Is the result different?

No Bob, but the thread title is... Read string variable while ignoring case sensitiveity

But then he said ... It strange that VBA doesn't have a function like UCASE or LCASE to ignore case sensitivity... so he clearly wasn't being as literal as you have interpreted it.

Bob, my post #12 wasn't in any way some sort of criticism of your reply, it was targeted at the OP's stated misconception (Post #10) that UCASE or LCASE somehow ignores case...