CODEWITHSUNDEEP
scriptinggoogle-apps-scriptgoogle-sheetsgoogle-docs
onji
onji

Reputation: 389

Google Sheets replaceText() Equivalent

In Google Sheets I have a column of cells, example below, which contains useful data - and some junk leftover from the source it came from. Basically I'm trying to write a script to remove "tag×" and anything that comes after it. I'm only interested in keeping the names

adam smith tag× production×
bill jones tag× sales×
chris pants tag× warehouse×
dave watts tag× phones×

I can do this in Excel with by looping through the column with
Selection.Replace What:="tag×*", Replacement:=""

But in my new office we're using Google Sheets. I thought I could do this with replaceText() but that function seems only available on the text class of the Document Service. I looked through the Spreadsheet App classes but can't find anything. Does anyone know if this kind of function exists in the Spreadsheet App, or how I could process this kind of column?

Upvotes: 1

Views: 908

Answers (4)

Shiva
Shiva

Reputation: 348

REGEXEXTRACT is another alternative, try this

=Arrayformula(iferror(REGEXEXTRACT(A2:A,"(^\w+\ \w+)")))

REGEXEXTRACT

result

Upvotes: 0

pleeyomi
pleeyomi

Reputation: 11

Create a column with this formula:

=left(A1,find("tag×",A1)-2)

Drag down adjacent to the column with the original data.

Screenshot here

Upvotes: 0

Tom Woodward
Tom Woodward

Reputation: 1713

Not fancy but assuming they were all structured like you demonstrated it could be done with two functions. Assuming your data is in column A2 and these functions are in b2 and c2 respectively. A working example is here.

=find("tag×",a2) =left(a2, b2-2)

Upvotes: 0

Serge insas
Serge insas

Reputation: 46802

You will have to use string manipulation.

Get all data in the column into an array and iterate row by row doing the string reduction. code below

function removeTags(){
  var column = 1
  var sh = SpreadsheetApp.getActive().getActiveSheet();
  var data = sh.getRange(1,column,sh.getLastRow(),1).getValues();
  for(var n in data){
    if(data[n][0]==''){continue};// if cell empty then continue
    var str = data[n][0].toString();
    data[n][0]= str.substring(0,str.indexOf('tag'));//remove everything after "tag"
  }
  sh.getRange(1,column,sh.getLastRow(),1).setValues(data);// update sheet with new data
}

Upvotes: 0

Related Questions