One way ..

Assuming source data within A2:A7
with the text to search entered in B2: car

Then in say, C2, array-entered*:
="A"&MATCH(TRUE,ISNUMBER(SEARCH(B2,A2:A7)),0)+1
returns the 1st instance: A2

And in say, C3, array-entered*:
="A"&MAX((ISNUMBER(SEARCH(B2,A2:A7)))*ROW(A2:A7 ))
returns the last instance: A5

*Press CTRL+SHIFT+ENTER to confirm the formula,
instead of just pressing ENTER

Replace SEARCH with FIND if you need the search to be case sensitive. SEARCH
is not case sensitive.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote in message
ps.com...
I need a formula that returns the cell location of the first instance
of a text string that I specify in a column listing. I also need a
formula that returns the cell location of the last instance of a text
string I specify in a column. For example lets say the text I want to
look for is 'car', The first formula would return A2 and the other
formula would return A5. I hope that makes sense. Can anyone help?

A1
A2 cars and trucks
A3 vans
A4 truck and racecar
A5 red car
A6 bean
A7 rice

Thanks!