Formula to extract an absolute location contained in another formula

[LKERN]

I have a column that contains a formula that includes an absolute reference to a cell on another worksheet.

sample formula:

Please Login or Register  to view this content.

I need to create another column that lists the absolute reference that is in that formula. (i.e. 'Recipes Entry'!$AA$8). Is there a way to do that?

I know that I can use FORMULATEXT to get the full formula, but I just need the cell reference part of the formula.

[jeffreybrown]

Somehow I suspect there is an easier way to do this, but how about...

=SUBSTITUTE(MID(FORMULATEXT(D4),FIND("'",FORMULATEXT(D4))+1,LEN(FORMULATEXT(D4))),")","")

[LKERN]

Thanks for the superfast reply, Jeff! It almost works perfectly. It cuts off the leading ' in the reference. What can I change to get that included?

[jeffreybrown]

Sorry about that. Remove the +1

[LKERN]

Stupendous! You saved me so much time. Thanks again for you help.

[jeffreybrown]

Glad it helped you and thanks for the feedback.