Cleaning the cell of any special characters, multiple occurences

Question

I have an interesting problem that I haven't found elsewhere. In VBA/Excel I could do it, but after switching to Google/Sheets I am lost :-). I need to use Google AppScript in Google Sheets to clean one cell from special characters that I have as a list defined on another sheet (one column, but I don't know how many characters (=rows) will be listed there, the list may expand).I want to replace these characters (e.g. ".", ",", "-", "_", etc.) in one particular cell with a space so that I can then easily split the cleaned text into words. These characters can occur multiple times in this one cell. Any ideas? Thanks for the tips!

Answer 1

I believe your goal is as follows.

• You want to convert (e.g. ".", ",", "-", "_", etc.) with a space using Google Apps Script.
• The values are one column.

In your situation, how about the following sample script?

Sample script:

function myFunction() {
  const search = [".", ",", "-", "_"];
  const replace = " ";

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // Please set your sheet name.
  sheet.getRange("A1:A" + sheet.getLastRow()).createTextFinder(`[${search.map(e => "\\" + e).join("")}]`).useRegularExpression(true).replaceAllWith(replace);
}

• In this sample script, the values of column "A" are converted. Please modify the column letter for your situation.
• For example, if you want to convert ... to which is the one space, please modify [${search.map(e => "\\" + e).join("")}] to [${search.map(e => "\\" + e).join("")}]+.

Reference:

• createTextFinder(findText) of Class Range