Find a word in a cell

[DeltekJJ]

I am writing a macro that requires me to look in a cell. In that cell there will be a string of text. In that text I am looking for a word. If that word is found I want it to return a 1. If not return a 0. For example if I am looking for the word "the" and it looks to cell A1. In A1 is the phrase "I like cake." It will return a zero. Then in Cell A2 is the phrase, "The eggs are the first ingrediant." it will return a 1. How do I do this?

 

[JimM]

Don't know if it's exactly what you want but you could give this formula a try

=IF(ISERR(SEARCH("*the word*",A1)),0,1)

 

[Banker1]

You can try something like this...<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Test()<SPAN style="color:#00007F">Dim</SPAN> WorkRange<SPAN style="color:#00007F">As</SPAN> Range<SPAN style="color:#00007F">Set</SPAN> WorkRange = Range("B1:B" & Range("A65536").End(xlUp).Row)<SPAN style="color:#00007F">For</SPAN><SPAN style="color:#00007F">Each</SPAN> Cell<SPAN style="color:#00007F">In</SPAN> WorkRange
    <SPAN style="color:#00007F">If</SPAN> UCase(Cell.Offset(0, -1).Value)<SPAN style="color:#00007F">Like</SPAN> "*" & "THE" & "*"<SPAN style="color:#00007F">Then</SPAN>
        Cell.Value = 1
    <SPAN style="color:#00007F">Else</SPAN>
        Cell.Value = 0
    <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><SPAN style="color:#00007F">Next</SPAN> Cell<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN></FONT>

Here is the sample I used....

Book1
	A	B	C	D
1	String	Result
2	I like cake, a lot	0
3	The eggs are first.	1
4	Top of the morning.	1
5	What the?	1
6	How are you doing?	0
Sheet1

 

[DeltekJJ]

Instead of puting in A1 can I put a varible that contains the active cell address?

 

[Nimrod]

Option Compare Text

Public Sub Demo()
    MsgBox ContainValue(Range("A1").Value, "*The*")
End Sub

Public Function ContainValue(CellValue, ComparValue) As Integer
If CellValue Like ComparValue Then
    ContainValue = 1
Else
    ContainValue = 0
End If
End Function