Google Sheets – How to Find and Replace Part of Text

google sheetsgoogle-apps-script

I would like to ask if it's possible to find & replace part of text in specified range and still preserve rest of the text as if you do it with Ctrl+H.

I am using this script 

function replace(){

  var sss=SpreadsheetApp.getActiveSpreadsheet();

  var ss=sss.getActiveSheet();

  var s=ss.getRange("B:B");

  var vlst=s.getValues();

  var i,j,a,find,repl;

  find="*Sample*";

  repl="*Result*";


for (i in vlst) {

  for (j in vlst[i]) {

    a=vlst[i][j];

    if (a>=find) vlst[i][j]=repl;

   }

 }

s.setValues(vlst);

}

but sadly it replaces all the text, i would need something like this :

sample(Hello world) —-> apple(Hello world) but instead i just get "apple"

How can I get it to only replace part of the text? Any help would be apreciated.

Best Answer

Changing if (a>=find) vlst[i][j]=repl; to vlst[i][j] = a.replace(find, repl); replaces the first occurance of find in each cell.

The linked documentation discusses more advanced ways to use String.replace() such as ignoring case.

https://www.w3schools.com/jsref/jsref_replace.asp

Related Topic