View Full Version : Solved: Read string variable while ignoring case sensitiveity
daniel_d_n_r
09-23-2007, 03:52 AM
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
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
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
Rory
thanks for pointing out my error.
I should know better than to post with a hangover
regards
qff
daniel_d_n_r
09-23-2007, 02:39 PM
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
09-23-2007, 03:02 PM
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
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
09-23-2007, 03:42 PM
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
09-24-2007, 04:08 AM
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
09-24-2007, 04:28 AM
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
09-24-2007, 04:40 AM
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
09-24-2007, 04:43 AM
Is the result different?
johnske
09-24-2007, 04:47 AM
No Bob, but the thread title is... Read string variable while ignoring case sensitiveity
Bob Phillips
09-24-2007, 04:53 AM
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
09-24-2007, 05:12 AM
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...
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.