Excel 2007 formula to return first cell reference in another cell's formula

[Shea]

Version: Excel 2007 WinXP

I'm basically looking for something almost like an inverse function to INDIRECT.
This function would first look at a cell's formula as a text string, parse out the first valid cell reference in A1 format, and return that cell as a text string.

Detail:
I have a spreadsheet with cells that point to other values. I would like to get only the row number from the first cell reference in the formula residing in a given cell. For example:

Suppose A1 has the formula =AL267.
and A2 has the formula =SUM(AL94:AL235)

I would like a formula in B1 that returns the text string, "AL267" so that I would know this is the first reference.

Ideally it could be dragged down to B2 such that it returns the text string "AL94" (and not "AL235") because AL94 is the first cell reference in A2's

Currently I am copying the formulas after hitting ctl+` and pasting that text into a text editor, followed by text operations to manipulate the results into the desired values. I was wondering if there might be a more elegant solution that didn't involve going out to notepad.

 

[Robert Mika]

You need this UDF

Function GetFormula(Cell as Range) as String
   GetFormula = Cell.Formula
End Function

Press ALT+F11, then Insert-->Modul an enter above code.
Then for A1 you can use
=GetFormula(A1)

And for A2:
=MID(LEFT(GetFormula(A2),FIND(":",GetFormula(A2))-1),FIND("(",LEFT(GetFormula(A2),FIND(":",GetFormula(A2))-1))+1,5)

 

[Shea]

Thanks. It was the cell.Formula property that I needed to know about. I had to do a little tweak to get read of the equals sign to use for my purposes, namely:

=RIGHT(getformula(A1),LEN(getformula(AT1))-1)

And I'm keeping the UDF in my personal workbook.