It is necessary to extract the text from the cell. the column contains a list in the form of text Name For Tb01 - 04/04/2018 ; you need to extract some text to the adjacent cell to the dash Name For Tb01 and to the next cell the date 04/04/2018 extract text from the cell using the formula is obtained, but it is possible Is it possible to do this using a script? Please tell me the script.

  • And what prevents just combine the script with the formulas? That is, the script put down the formula, then insert the data as values. If the script is pure - look towards the split - Sergey Pryanichkin
  • In this case, a script is needed, since the list is very large, and the formula can be stretched for a very long time and the list may be different. - Sergun

1 answer 1

All the same, it is a little incomprehensible than the usual formulas displeased you, therefore I will describe all 3 options here:

1. Pure formulas and auto-complete

Suppose there is data in column A.

enter image description here

Next to cell B1, you write the formula: =IF(ISBLANK(A1);"";SPLIT(A1;" - ";FALSE)) and simply double-click the lower square of the cell and it is auto-filled down by the amount of data.

enter image description here

Or you can use an array formula with a check for empty lines (so as not to bother with the exact range indicated (also enter in cell B1. You do not need to auto-fill down. Just remember to specify the format of the cell with the date as the date - otherwise there may be numbers instead)

 =ARRAYFORMULA(IF(ISBLANK(A1:A);"";SPLIT(A1:A;" - ";FALSE)))

2. Script + formula

We do the same, only the introduction of formulas is not a manual, but a script. You can also insert data as a value (so that there are no formulas):

 function myFunction10() { var ss = SpreadsheetApp.getActive() var sheet = ss.getSheetByName("sheetName") /* вариант с 1 формулой */ var range = sheet.getRange(1, 2, sheet.getLastRow()) var range2 = sheet.getRange(1, 2, sheet.getLastRow(), 2) range.setFormula("=IF(ISBLANK(A1);\"\";SPLIT(A1;\" - \";FALSE))") SpreadsheetApp.flush() range2.copyTo(range2, {contentsOnly:true}) /* вариант с 2 формулой */ var range = sheet.getRange(1, 2) var range2 = sheet.getRange(1, 2, sheet.getLastRow(), 2) range.setFormula("=ARRAYFORMULA(IF(ISBLANK(A1:A);\"\";SPLIT(A1:A;\" - \";FALSE)))") SpreadsheetApp.flush() range2.copyTo(range2, {contentsOnly:true}) }

3. Script (work with arrays)

 function myFunction11() { var ss = SpreadsheetApp.getActive() var sheet = ss.getSheetByName("sheetName") var arrData = sheet.getRange(1, 1, sheet.getLastRow()).getDisplayValues() var newArr = arrData.map(function(v,i,a){ return v[0].split(" - ") }) sheet.getRange(1, 2, newArr.length, 2).setValues(newArr) }