Retrieve a cell's formula, not its result

Question

In Excel, I may have a column of names beside a column of hyperlinks to files related to each name. In another sheet I want to extract the path to the file, NOT the text supplied by the HYPERLINK function. For example:

Name    Function
Amy     =HYPERLINK("Users\My Documents\amy000.pdf","Yes")
Bob     =HYPERLINK("Users\My Documents\robert.pdf","Yes")
Charlie =HYPERLINK("Users\My Documents\charli.pdf","No")
Dan     =HYPERLINK("Users\My Documents\daniel.pdf","Yes")

What displays in each function cell is "Yes" or "No".

What I want to do is something like

MID(Sheet1!B2,13,29)

to pull out the text string Users\My Documents\amy000.pdf from inside the formula. I want to do this without formatting the original formulas as text.

=CELL("contents",B2) didn't work. Everything I've tried with VLOOKUP, INDEX and MATCH deal only with the "Yes" or "No" result values, not the original function.

Thanks!

http://stackoverflow.com/questions/9122046/excel-getting-formula-of-another-cell-in-a-cell-without-vba — rscarson, Aug 12 '16 at 15:00

score 1 · Accepted Answer · edited May 23 '17 at 11:51

1

Starting with Excel 2013, FORMULATEXT() provides what you are looking for.

Otherwise, you could use the workaround from the solution of Getting formula of another cell in target cell.

edited May 23 '17 at 11:51

Community

1
1

answered Aug 12 '16 at 15:14

Ulli Schmid

1,167
1
8
16

Thank you. FORMULATEXT worked together with INDEX & MATCH, but didn't work for me with VLOOKUP. – Sharpasamarble Aug 12 '16 at 15:26

Retrieve a cell's formula, not its result

1 Answers1