In your sample Spreadsheet, it seems that the cells "A2, "B2" are the values of https://www.harrods.com/en-gb/shopping/women-clothing/brands and 4 Moncler Simone Rocha, respectively. And, at the output column, the cell "C2" is the formula of =CONCATENATE($A$2,"#dcp=1&dppp=100&OrderBy=rank&Filter=ABRA%5E",B2). In this case, how about modifying the formula as follows?

From:

=CONCATENATE($A$2,"#dcp=1&dppp=100&OrderBy=rank&Filter=ABRA%5E",B2)

To:

=CONCATENATE($A$2,"#dcp=1&dppp=100&OrderBy=rank&Filter=ABRA%5E",SUBSTITUTE(B2," ","%20"))

• By this modification, https://www.harrods.com/en-gb/shopping/women-clothing/brands#dcp=1&dppp=100&OrderBy=rank&Filter=ABRA%5E4%20Moncler%20Simone%20Rocha is obtained at the cell "C2".

If you want to achieve this using Google Apps Script, how about the following script?

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet and save it. And when you want to run this script with an image button, please put an image to the Spreadsheet and assign the function name of myFunction to the button. By this, when you click the button, the script works. And, when the script is run, a single space of the values of column "B" are replaced with %20.

function myFunction() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Filter URLs");
  sheet.getRange("B2:B" + sheet.getLastRow()).createTextFinder(" ").replaceAllWith("%20");
}

Note:

• As additional information, when you want to convert your current formula with Google Apps Script, you can also use the following script.

    function myFunction2() {
      const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Filter URLs");
      const url = sheet.getRange("A2").getValue();
      const values = sheet.getRange("B2:B" + sheet.getLastRow()).getValues().map(([b]) => [url + "#dcp=1&dppp=100&OrderBy=rank&Filter=ABRA%5E" + b.replace(/ /g,"%20")]);
      sheet.getRange(2, 4, values.length, 1).setValues(values);
    }
  

  • When this script is run, the URLs are put to the column "D". When you want to put the column "C", please modify getRange(2, 4, values.length, 1) to getRange(2, 3, values.length, 1).

References:

• SUBSTITUTE
• createTextFinder(findText) of Class Range
• Class TextFinder

I've added a new sheet ("Filter URLs - Erik") to your sample spreadsheet. There, I deleted everything in Column C (including the header) and placed the following single formula in cell C1:

=ArrayFormula({"Output";IF(B2:B="",,$A$2&"#dcp=1&dppp=100&OrderBy=rank&Filter=ABRA%5E"&SUBSTITUTE(TRIM(B2:B)," ","%"))})

Expanding on one of @Tanaike's points, this one formula will create the header and all results for all rows without the need for dragging the formula, using scripts or using other Find/Replace methods.

If you're only looking to create urls, use

=ENCODEURL(B2)

instead. This will automatically convert all non url characters to it's percent encoding.