Solved: Read string variable while ignoring case sensitiveity

[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

[qff]

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
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

regards
qff

[rory]

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:
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

[qff]

Rory

thanks for pointing out my error.

I should know better than to post with a hangover

regards
qff

[rory]

:)

[daniel_d_n_r]

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

[Bob Phillips]

I would use



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

[rory]

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.

[johnske]

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?

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


Option Explicit

Option Compare Text

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

[daniel_d_n_r]

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

thanks all for the helpful assistance

cheers

[Bob Phillips]

It does if you want that



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

[johnske]

It does if you want that



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
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...

[Bob Phillips]

Is the result different?

[johnske]

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

[Bob Phillips]

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.

[johnske]

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...