Look to see if a cell contains something

drewm · Apr 22nd, 2002, 06:37 AM

Is there any way for one cell to look to seee if another cell contains something with a long string of text. I want to make a conditional statement that does one thing if a cell contains the word "drop", however the word "drop" may be within a sentence that will be several words long. The sentence length will vary. Is there any way to ferret out a single word?

Mark W. · Apr 22nd, 2002, 07:18 AM

=IF(OR(ISNUMBER(MATCH({"drop","drop *","* drop *","* drop"},A1,0))),"does one thing","does another")

[ This Message was edited by: Mark W. on 2002-04-22 06:20 ]

drewm · Apr 22nd, 2002, 08:10 AM

=IF(OR(ISNUMBER(MATCH({"drop","drop *","* drop *","* drop"},A1,0))),"does one thing","does another")

Worked fantastic, thanks a lot!!!

nisht · Apr 22nd, 2002, 08:22 AM

Write down this code in selection change event of the sheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If InStr(1, CStr(Target), "drop") Then
' code for conditional formating
Target.Interior.ColorIndex = 35
End If

i hope this will work.

nishith desai
http://www.pexcel.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 22nd, 2002, 06:37 AM	#1
drewm New Member Join Date: Apr 2002 Posts: 7	Is there any way for one cell to look to seee if another cell contains something with a long string of text. I want to make a conditional statement that does one thing if a cell contains the word "drop", however the word "drop" may be within a sentence that will be several words long. The sentence length will vary. Is there any way to ferret out a single word?

Apr 22nd, 2002, 07:18 AM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	=IF(OR(ISNUMBER(MATCH({"drop","drop "," drop "," drop"},A1,0))),"does one thing","does another") [ This Message was edited by: Mark W. on 2002-04-22 06:20 ]

Apr 22nd, 2002, 08:10 AM	#3
drewm New Member Join Date: Apr 2002 Posts: 7	=IF(OR(ISNUMBER(MATCH({"drop","drop "," drop "," drop"},A1,0))),"does one thing","does another") Worked fantastic, thanks a lot!!!

Apr 22nd, 2002, 08:22 AM	#4
nisht Board Regular Join Date: Feb 2002 Location: Ahmedabad Gujarat Posts: 303	Write down this code in selection change event of the sheet. Private Sub Worksheet_SelectionChange(ByVal Target As Range) If InStr(1, CStr(Target), "drop") Then ' code for conditional formating Target.Interior.ColorIndex = 35 End If i hope this will work. nishith desai http://www.pexcel.com