Formula from string

Question

I have a string in a cell in Google Sheets that I build (that's why it is a string)

index(split(D9,"/"),1)

Now i need to turn this as a real formula - the opposite of formulatext()

something like TextToFormula() !

I usually use the indirect function, but when including a formula, it does not work

Any idea how to proceed ?

if you post about what the real problem is that you're trying to address with this solution, it might be easier to show you a workaround. — MattKing, Nov 11 '20 at 19:37

score 0 · Answer 1 · answered Nov 11 '20 at 20:51

you may use script which turns texted formula into real formula:

function onEdit() { 
var sheet = SpreadsheetApp.getActive().getSheetByName('Master Sheet');  //sheet name
var src = sheet.getRange("A1");    // The cell which holds the formula
var str = src.getValue();  
var cell = sheet.getRange("C5");   // The cell where I want the results to be
cell.setFormula(str);              
}

Rubén · Answer 2 · 2020-11-11T21:35:22.180

0

There is no built-in function to convert a text into a spreadsheet formula. If you are open to use an script, you migth use the setFormula method.

Resources

https://developers.google.com/apps-script/guides/sheets

answered Nov 11 '20 at 21:23

Rubén

29,320
9
61
145

score 0 · Answer 3 · answered Nov 12 '20 at 08:57

In order to transform the string into a formula, you should use the setFormula() function from Apps Script:

var sheet = SpreadsheetApp.getActiveSheet();
var formula = sheet.getRange("RANGE_FOR_THE_STRING").getValue();
sheet.getRange("RANGE_WHERE_YOU_WANT_TO_SET_THE_FORMULA").setFormula("="+formula);

Depending on the structure of your string, you might need to some tweaking in order to transform it into an accurate formula, hence the = added in the code above.

Reference

Apps Script Class Range - setFormula(formula).

Formula from string

3 Answers3

Reference