Extract portion of formula resident in a cell

[Guest]

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"

 

[Peo Sjoblom]

You can't without using VBA or excel4 macros, why would you think that? You
can't have the cake and eat it, of course if you format the cell as text
before you type in the source formula you can do this, I doubt you want that
though

http://www.mvps.org/dmcritchie/excel/formula.htm


shows a UDF that will return the formula as a text string, then you can wrap
it in the RIGHT function


=RIGHT(getformula(B5),4)

 

[Ron Rosenfeld]

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