On Wed, 29 Aug 2007 07:46:05 -0700, JASelep wrote:

I want to extract a cell reference from a formula in a cell

I tried the right function referenceing the cell but it returns the right
portion of the function result

how do I specify it to extract the physical formula text in the cell and not
the formula value in the cell?

example... a cell b5 contains the formula "='Execute Dealer Plan'!$P647"
which has a value of "select status"
if cell b6 contains formula "=right(b5,4) I'm wanting "P647" returned and
not "atus"


You cannot do this with a built-in excel function. The simplest method would
be to use a UDF to return the cell formula as text. (You can also do this
using Excel 4.0 XLM macro techniques, but I think that is more convoluted).

To enter the UDF, <alt-F11 opens the VBEditor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code below into the window that opens.

To use this, =getformula(cell_ref) will return the formula in the cell as text.
You can then use MID, LEFT and RIGHT or other text processing methods to return
the desired portion of the formula.

================================
Function GetFormula(cell_ref As Range) As String
GetFormula = cell_ref.Formula
End Function
===============================
--ron