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

Question

I have 0,4*A1 in a cell (as a string). How to convert this “row formula” to a valid formula and calculate its value in another cell?

Answer 1

Eval may come up:

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

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

Answer 2

I combined my formula as usual, but in the beginning I had '= instead of = .

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

Its several stages, but it avoids VBA.

I hope this helps,

Rob

Answer 3

Just for fun, I found an interesting article here to use some kind of hidden rating function that exists in Excel. The trick is to assign a name to it and use the name in your cells, because EVALUATE () will give you an error message if it will be used directly in the cell. I tried and it works! You can use it with a relative name if you want to copy lines by lines.

Answer 4

UPDATE This worked (in 2007, I reckon), but not in Excel 2013.

EXCEL 2013: This is not quite the same, but if you can put 0.4 in one cell (e.g. B1) and the text value A1 in another cell (C1, say), in cell D1, you can use = B1 * INDIRECT ( C1), which leads to the calculation of the value 0.4 * A1. So, if A1 = 10, you get 0.4 * 10 = 4 in cell D1. I will update again if I find the best solution for 2013, and I'm sorry that Microsoft destroyed the original INDIRECT functionality!

EXCEL 2007 Version: For a solution other than VBA, use the INDIRECT formula. It takes a string as an argument and converts it into a cell reference.

For example, = 0.4 * INDIRECT ("A1") will return a value of 0.4 * the value that is in cell A1 of this sheet.

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

Answer 5

I prefer the vba solution for professional solutions.

With parts of the replacement procedure in question, to search and replace only FULL WORDS , I use the following vba procedure:

 '' ' Evaluate Formula-Text in Excel ' Function wm_Eval(myFormula As String, ParamArray variablesAndValues() As Variant) As Variant Dim i As Long ' ' replace strings by values ' For i = LBound(variablesAndValues) To UBound(variablesAndValues) Step 2 myFormula = RegExpReplaceWord(myFormula, variablesAndValues(i), variablesAndValues(i + 1)) Next ' ' internationalisation ' myFormula = Replace(myFormula, Application.ThousandsSeparator, "") myFormula = Replace(myFormula, Application.DecimalSeparator, ".") myFormula = Replace(myFormula, Application.International(xlListSeparator), ",") ' ' return value ' wm_Eval = Application.Evaluate(myFormula) End Function '' ' Replace Whole Word ' ' Purpose : replace [strFind] with [strReplace] in [strSource] ' Comment : [strFind] can be plain text or a regexp pattern; ' all occurences of [strFind] are replaced Public Function RegExpReplaceWord(ByVal strSource As String, _ ByVal strFind As String, _ ByVal strReplace As String) As String ' early binding requires reference to Microsoft VBScript ' Regular Expressions: ' with late binding, no reference needed: Dim re As Object Set re = CreateObject("VBScript.RegExp") re.Global = True 're.IgnoreCase = True ' <-- case insensitve re.Pattern = "\b" & strFind & "\b" RegExpReplaceWord = re.Replace(strSource, strReplace) Set re = Nothing End Function 

Using the procedure in an Excel worksheet is as follows:

Answer 6

In my opinion, the best solutions are in this link: http://www.myonlinetraininghub.com/excel-factor-12-secret-evaluate-function

Here is the summary: 1) In cell A1, enter 1, 2) In cell A2, enter 2, 3) In cell A3, enter +, 4) Create a named range with "= Evaluate (A1 and A3 and A2)" in the field refers to the field when creating a named range. Lets call this named range "testEval", 5) In cell A4, enter = testEval,

Cell A4 must have a value of 3 in it.

Notes: a) Does not require programming / vba. b) I did it in Excel 2013 and it works.

Answer 7

The best non-VBA way to do this is to use the TEXT formula. It takes a string as an argument and converts it to a value.

For example, = TEXT ("0.4 * A1", '##') will return the value 0.4 * the value that is in cell A1 of this worksheet.