Opposite of FORMULATEXT in Excel

Is there a function that does the opposite of FORMULATEXT

For example in Cell B2 I have:

='[Workshop Schedule 2020.xlsx]Lisburn'!D7

In C2 i am using FORMULATEXT and REPLACE, to replace LISBURN with CANNOCK:

=REPLACE(FORMULATEXT(B2),32,7,"CANNOCK")

Which returns the below as a string.

='[Workshop Schedule 2020.xlsx]CANNOCK'!D7

I now want to use this string as a formula. I cannot use INDIRECT as it points to an external workbook.

Solution 1:

You could create a User Defined function like:

Public Function eval(s As String) As Variant
    eval = Evaluate(s)
End Function

So if A1 contains the text string:

=1/2 & " some text"

Then:

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=eval(A1)

To learn more about macros in general, see:

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

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

Solution 2:

I laud your willingness to use VBA and the UDF's that make so many things easier. UDF's are a great place to become comfortable with VBA for those who are not so it's a gateway use as well as often a great solution.

You CAN do it without a UDF if you wish though. Very fast to do and requires no planning of approach like a UDF often does since you are going to let Excel do the work of figuring out how to evaluate the result.

It also has the point of being a direct Yes to your desire for something akin to the INDIRECT() function.

You can use the old macro language (25 years old) function EVALUATE. It is available BUT ONLY IN THE NAMED RANGE FUNCTION. (And in macros, but a good macro has already been done by Gary's Student.) Typing the function into the sheet itself will fail.

Create a Named Range (perhaps named "horsey"). For its Refers to value enter the following formula:

=EVALUATE(C2)

and save things, closing the Named Range manager.

Now typing

=horsey

into ANY cell on the sheet (well, except B2 and C2!) will do exactly what INDIRECT() would do with a cell reference. It takes the string (including the "=" or NOT including it makes no difference) in C2 and treats it as a formula.

People often think of EVALUATE() in the restricted role of turning some cell entry like 17-5+32 into a calculated result even though it was text and had no equal sign, so they figure it's of no great value. But it can do hugely more involved stuff, like exactly what you are asking for today.

Especially since we now have FORMULATEXT() so we can easily modify working formulas.