Going Bananas

[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

 

[Chip Pearson]

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

 

[David McRitchie]

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

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

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

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