0

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.

1 Answer 1

2

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

2
  • thank you , i had to change it like that: if (a>=find) vlst[i][j] = a.replace(find, repl); to work, but i found out one more problem i have to replace value trough multiple sheets is it possible to extend "multisheet" range ? i dont want to have 20 times same code and changing it daily
    – Exelus
    Apr 20, 2018 at 7:28
  • var ss = sss.getSheets() will make ss an array of all the sheets. which you can then loop thru with for (x in ss[x]) Apr 20, 2018 at 12:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.