How to turn a string formula into a "real" formula?

I have 0,4*A1 in a cell (as a string). How can convert this "string formula" into a real formula and calculate its value, in another cell?

Solution 1:

Evaluate might suit:

http://www.mrexcel.com/forum/showthread.php?t=62067

Function Eval(Ref As String)
    Application.Volatile
    Eval = Evaluate(Ref)
End Function

Solution 2:

I concatenated my formula as normal, but at the start I had '= instead of =.

Then I copy and paste as text to where I need it. Then I highlight the section saved as text and press ctrl + H to find and replace.
I replace '= with = and all of my functions are active.

It's a few steps, but it avoids VBA.

Solution 3:

UPDATE This used to work (in 2007, I believe), but does not in Excel 2013.

EXCEL 2013:

This isn't quite the same, but if it's possible to put 0.4 in one cell (B1, say), and the text value A1 in another cell (C1, say), in cell D1, you can use =B1*INDIRECT(C1), which results in the calculation of 0.4 * A1's value.

So, if A1 = 10, you'd get 0.4*10 = 4 in cell D1. I'll update again if I can find a better 2013 solution, and sorry the Microsoft destroyed the original functionality of INDIRECT!

EXCEL 2007 version:

For a non-VBA solution, use the INDIRECT formula. It takes a string as an argument and converts it to a cell reference.

For example, =0.4*INDIRECT("A1") will return the value of 0.4 * the value that's in cell A1 of that worksheet.

If cell A1 was, say, 10, then =0.4*INDIRECT("A1") would return 4.

Solution 4:

Just for fun, I found an interesting article here, to use a somehow hidden evaluate function that does exist in Excel. The trick is to assign it to a name, and use the name in your cells, because EVALUATE() would give you an error msg if used directly in a cell. I tried and it works! You can use it with a relative name, if you want to copy accross rows if a sheet.