Consulting

Results 1 to 16 of 16

Thread: Solved: Read string variable while ignoring case sensitiveity

  1. #1

    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

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

  3. #3
    VBAX Master
    Joined
    Jun 2007
    Location
    East Sussex
    Posts
    1,110
    Location
    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]
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Rory

    thanks for pointing out my error.

    I should know better than to post with a hangover

    regards
    qff

  5. #5
    VBAX Master
    Joined
    Jun 2007
    Location
    East Sussex
    Posts
    1,110
    Location
    Regards,
    Rory

    Microsoft MVP - Excel

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

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  8. #8
    VBAX Master
    Joined
    Jun 2007
    Location
    East Sussex
    Posts
    1,110
    Location
    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Administrator
    Chat VP
    VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    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]
    You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you

    The major part of getting the right answer lies in asking the right question...


    Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.

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

    thanks all for the helpful assistance

    cheers

  11. #11
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  12. #12
    Administrator
    Chat VP VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    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...
    You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you

    The major part of getting the right answer lies in asking the right question...


    Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.

  13. #13
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Is the result different?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  14. #14
    Administrator
    Chat VP VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    No Bob, but the thread title is... Read string variable while ignoring case sensitiveity
    You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you

    The major part of getting the right answer lies in asking the right question...


    Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.

  15. #15
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  16. #16
    Administrator
    Chat VP VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    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...
    You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you

    The major part of getting the right answer lies in asking the right question...


    Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •