return the reference of a cell with certain text in it

[Kevin]

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

 

[Ron Rosenfeld]

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

 

[RagDyer]

Say your range to search was A1 to D100.
Enter the string to find in E1, then try this *array* formula:

=ADDRESS(MAX((A1100=E1)*ROW(A1100)),
MAX((A1100=E1)*COLUMN(A1100)),4)

--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

BTW ... the cell reference returned by this formula is text, and cannot be
used as is, in another formula to reference that cell.

It must be wrapped in Indirect() to work as an actual cell reference.