Going Bananas

  • Thread starter Thread starter Lexette
  • Start date Start date
L

Lexette

Hi and HELP

If cell = " banana" then is great when banana is the
only content in the cell. I have cells that contain "
banana/1234567" and the number can change every time but I
still want to do something with the contents of the cell
because it contains the word "banana" is there a way to
say If cell [contains the word] "Banana" Then

Many Thanks Lexette
 
Lexette,

You can use the Like operator with wildcards to test the contents
of the cell. For example,

If Range("A1").Value Like "*banana*" Then

You could also use the InStr function. E.g.,

If InStr(1,Range("A1").Value,"banana") > 0 Then



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Hi Lexette,
For what it's worth both of the examples are case sensitive,
you can use LCASE(Range("A1").Value to use the
examples as not case sensitive. The InStr function has
xlTextCompare that can be used instead.

Actually it is the string banana that is being checked, How you
interpret contains the word banana may not be so clear..
i.e. xbanana republic does or does not contain the word banana

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
MsgBox InStr(1, Range("A1").Value, "banana") > 0 'case sensitive
MsgBox InStr(1, Range("A1").Value, "banana", vbTextCompare) > 0 'insensitive
MsgBox Range("A1").Value Like "*banana*" 'case sensitive
MsgBox LCase(Range("A1").Value) Like "*banana*" 'case insensitive
End Sub



Chip Pearson said:
Lexette,

You can use the Like operator with wildcards to test the contents
of the cell. For example,

If Range("A1").Value Like "*banana*" Then

You could also use the InStr function. E.g.,

If InStr(1,Range("A1").Value,"banana") > 0 Then

Lexette said:
If cell = " banana" then is great when banana is the
only content in the cell. I have cells that contain "
banana/1234567" and the number can change every time but I
still want to do something with the contents of the cell
because it contains the word "banana" is there a way to
say If cell [contains the word] "Banana" Then

Many Thanks Lexette
Click to expand...
Click to expand...
 
Back
Top