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

User · Answer

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  quot A1 quot   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  quot A1 quot   would return 4

User · Answer

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  amp  A3  amp  A2   in the refers to field while creating the named range  Lets 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

User · Answer

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    Its a few stages but it avoids VBA    I hope this helps    Rob

User · Answer

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

User · Answer

Say  let we have column E filled by formulas that returns string  like              amp  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 amp R    No mistakes  no typo  but stupid mechanical actions instead only     Like on a Ford conveyor  And in just a few seconds only       Assume  all of involved columns are in  General  format     Open  Notepad   Select entire column D Ctrl C   Ctrl V in NPP Ctrl A in NPP Select cell in the first row of desired column G1 Ctrl V Enjoy

User · Answer

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

User · Answer

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    lt -- case insensitve     re Pattern     b   amp  strFind  amp    b      RegExpReplaceWord   re Replace strSource  strReplace      Set re   Nothing End Function   Usage of the procedure in an excel sheet looks like

User · Answer

Evaluate might suit   http   www mrexcel com forum showthread php t 62067  Function Eval Ref As String      Application Volatile     Eval   Evaluate Ref  End Function

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

EXCEL 2013:

EXCEL 2007 version:

Examples related to string

Examples related to excel

Examples related to formula

Examples related to evaluate