On Fri, 30 Nov 2007 09:41:13 -0800, Kevin
wrote:

Hello all,

I would like to find a formula that will search for the cell containing the
word "Doctor 1" and return the reference (location) of that cell (such as A1
or R1C1).

thanks

Here is a UDF that will do that.

RngToSearch is the Range of cells to be searched.
StrToFind is the text string you are searching for. IT can be a string literal
or a cell reference.

You would use this on a worksheet as:
=wordaddress(A18,rng)

To enter it, <alt-F11 opens the VB Editor. Ensure your project is highlighted
in the Project Explorer window, then Insert/Module and paste the code below
into the window that opens:

================================================== =========================
Option Explicit
Function WordAddress(StrToFind As String, RngToSearch As Range) As String
Dim c As Range
For Each c In RngToSearch
If InStr(1, c.Text, StrToFind) < 0 Then
WordAddress = c.Address
Exit Function
End If
Next c
End Function
=============================================





--ron