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!
CodePudding user response：
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[${search.map(e => "\\" e).join("")}]to[${search.map(e => "\\" e).join("")}].