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

Question

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?

Weightlessness

Excel function that evaluates a string as if it were a formula? – Bowel

Could you do it the other way around? From Excel 2013 onwards, FORMULATEXT() would show you the formula that created the result. From the end-user's point of view, the values in both cells would look the same as what you seem to be asking for. – Peculation

Answer 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

Ulmaceous

Be careful if using this approach: Evaluate() by default will operate in the context of the active sheet so the output of something like Eval("0.4*A1") will change depending on which worksheet is active. You can use (eg) Application.ThisCell.Parent.Evaluate(Ref) to constrain the context to the sheet containing the call to Eval – Capriccioso

@PrzemyslawRemin yes, it still works at least to Excel 2016 – Bowel

This works for me, but when the string to evaluate was too long, it started failing with #VALUE! I think the string must be strictly under 256 characters (Excel 2010) – Ventriloquist

Answer 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.

Gamesome

This is a winner! I used @ because ' was a character in my formulas – Philpott

You don't event need apostrophe: it's enough to replace = with itself – Emmaemmalee

Brilliantly simple! I found that just finding and replacing the = sign worked for me because I didn't have the ' character at the start. – Emulate

You can also use this method if you want to make repetitive, similar edits across multiple cells using Find/Replace function. Disable the = with /= @= or whatever you want to temporarily disable formula and allow formula to show as text. Then Find/Replace what you really want to change. The final Find/Replace will change /= @= etc back to just = and it will be self-calculating formula again. No formatting changes. – Steddman

This is absolutely GENIUS and has just saved me hours and hours of time ! – Kirt

Unfortunately for me (Excel 365 v2102) Find & Replace would not find the leading ' Instead I copy to notepad and paste back which works ok – Aspia

This is NOT dynamic though is it. You'd need to cut and paste after any update in your file. – Spire

Answer 3

UPDATE This formula works in Microsoft 365 as explained for 2007. Original text: This used to work (in 2007, I believe), but does not in Excel 2013.

EXCEL Microsoft 365

Let´s say we want to make a reference to the cell 'Tab 1'!C3, but weu want to make that reference from the tab name in a column and the cell name in a row, so the reference is buit out of:

CONCAT("'",$B2,"'!",D$1)

with B2 containing the value Tab 1 and D1 containing value C3.

The INDIRECT formula works this way

=INDIRECT(CONCAT("'",$B2,"'!",D$1))

Please, be aware of the required "'" at the beginning.

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.

Ferreous

for more information and useful examples see - contextures.com/xlFunctions05.html and cpearson.com/excel/indirect.htm – Argal

I'm intrigued by the 16 up-votes - from what I understand of the indirect function this is never going to work. I can see how you might do something like =INDIRECT("A"&(0.4*5)) i.e. using maths to calculate the reference of the cell, but I'm pretty sure indirect won't calculate anything other than a reference. – Declarative

This approach does not work - how can we fix this answer? – Capriccioso

The reason it doesn't work, is because the formula is used wrong. The correct formula is =0.4*INDIRECT(A1) – Tomasine

@Brunox13 - reversed the approved edit. Really, this answer should just be deleted. There's a reason it has 26 downvotes as of writing this comment. – Authoritative

@Authoritative Why is that? – Deshabille

Because it doesn't work. It is not a valid answer to the original question. – Authoritative

This was the way I managed to do the intended task in a couple of minutes, it helps me a lot. I needed to put some quotes on the formula, but it did the work on a Microsoft 365 (Office) in 2023. Here my working example =INDIRECT(CONCAT("'",$B28,"'!",D$1)) with B28 a name of a Tab and D1 a name of a Cell. – Morbihan

Answer 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.

Agent

Interesting but didn't seem to apply to newer versions of Excel. If I read it correctly it was a trick to access suppressed 1990's Excel behavior into early 2000's Excel, but out of date in 2010, 2013, office 365, etc? – Gravelblind

Nothing is "out of date" if it works! 4.0 Macro language offers a few neat tricks. – Demagoguery

Sadly, there is no =EVALUATE() function in Excel 2013. – Ferreous

It still does work in Excel for Mac 2011 - see [this] (superuser.com/questions/253353/…) – Pentateuch

It still works with Excel 2013. I just tested it, But you have to go through the Name trick described in the link. – Agent

The Name trick will still call the EVALUATE function from VBA, so you'll need to save as macro-enabled (xlsm) – Agro

Answer 5

I prefer the VBA-solution for professional solutions.

With the replace-procedure part in the question search and replace WHOLE WORDS ONLY, 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

Usage of the procedure in an excel sheet looks like:

Stockholm

Answer 6

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

Here is a 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 & A3 & A2) in the refers to field while creating the named range. Let's call this named range "testEval",
5. In cell A4 enter =testEval,

Cell A4 should have the value 3 in it.

Notes:
a) Requires no programming/VBA.
b) I did this in Excel 2013 and it works.

Oscilloscope

The given example did not work, but the example at the link did work. This fit the bill. Thanks. – Bergama

Perfect solution for an excel user – Kohn

Answer 7

Say, let we have column E filled by formulas that returns string, like:

= " = " & D7

where D7 cell consist more complicated formula, that composes final desired result, say:

= 3.02 * 1024 * 1024 * 1024

And so in all huge qty of rows that are.

When rows are a little - it just enough to copy desired cells as values (by RMB)
to nearest column, say G, and press F2 with following Enter in each of rows.
However, in case of huge qty of rows it's impossible ...

So, No VBA. No extra formulas. No F&R

No mistakes, no typo, but stupid mechanical actions instead only,

Like on a Ford conveyor. And in just a few seconds only:

1. [Assume, all of involved columns are in "General" format.]
2. Open Notepad++
3. Select entire column D
4. Ctrl+C
5. Ctrl+V in NPP
6. Ctrl+A in NPP
7. Select cell in the first row of desired column G1
8. Ctrl+V
9. Enjoy :) .

Fatness

The Notepad solution worked for me – Aspia

Answer 8

Excel 2019 here. EVALUATE isn't valid.

It would work if we created a Named Range out of it:

But in this case we provide an absolute reference, which is not nice:

1. We would have to modify the formula every time we want to reuse it.
2. When the value in A1 changes, the evaluated result would not change.

Solution:

=EVALUATE(GET.CELL(5,OFFSET(INDIRECT("RC",FALSE),0,-1)))

Littoral

Answer 9

The best, non-VBA, way to do this is using 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 of 0.4 * the value that's in cell A1 of that worksheet.

Endaendall

This doesn't work, and the formula is wrong (" needed instead of ' around ##.) – Ferreous