1

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 ?

Rubén
  • 34,714
  • 9
  • 70
  • 166
BTC75
  • 71
  • 10
  • 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

4 Answers4

1

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

Related

Rubén
  • 34,714
  • 9
  • 70
  • 166
0

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);              
}
player0
  • 124,011
  • 12
  • 67
  • 124
0

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

ale13
  • 5,679
  • 3
  • 10
  • 25
0

Since the addition of LAMBDA this is possible without GAS. I have created an example sheet here: INDIRECT with ARRAYFORMULA Example

traptw1thin
  • 1
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Mar 10 '23 at 20:15