Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Going Bananas
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Going Bananas
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 "Lexette" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Going Bananas
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 "Chip Pearson" wrote in message ... 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" wrote 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|