Replace All and reduce text string-CodePudding

I have Google Forms results from live workshops that I want to edit in Sheets. I then want to calculate averages, pivot etc. to explore the data for insights.

I want to clean and standardize the data. I'd like to replace a series of long strings and reduce them just to their leading number:

Example:

I'm aware of certifications I should be starting.

Replace with

3

Example 2:

I'm currently progressing a powerful certification.

Replace with:

4

In Excel I would simply use * as wildcard for the rest of the string, but Sheets appears different. I've read documentation and posts about regular expressions etc. and I'm not sure if that's overkill or how to proceed.

I'd THEN like to create a macro which does that for the whole sheet:

All strings which begin with 1)*

--> Replace with 1

All strings which begin with 2)*

--> Replace with 2

CodePudding user response：

try:

=REGEXEXTRACT(A1; "^\d ")

CodePudding user response：

I believe your goal is as follows.

You want to convert the following situations in a sheet in a Google Spreadsheet.

From

  3. I'm aware of certifications I should be starting.
  4. I'm currently progressing a powerful certification.

To
```
  3
  4
```

From

  All strings which begin with 1)*
  All strings which begin with 2)*

To
```
  1
  2
```

You want to achieve this using Google Apps Script.

In this case, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor. And, please set the sheet name and run the function. By this, the above situations are done.

function myFunction() {
  const sheetName = "Sheet1"; // Please set your sheet name.
  SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName(sheetName)
    .createTextFinder("(\\d )\\.. |.  (\\d )\\). ")
    .useRegularExpression(true)
    .matchEntireCell(true)
    .replaceAllWith("$1$2");
}

Or, how about the following sample script?

function myFunction() {
  const sheetName = "Sheet1";
  SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName(sheetName)
    .createTextFinder("[^\\d] ")
    .useRegularExpression(true)
    .replaceAllWith("");
}

If you want to use this script in all sheets in a Google Spreadsheet, you can also use the following script.

function myFunction() {
  SpreadsheetApp
    .getActiveSpreadsheet()
    .createTextFinder("(\\d )\\.. |.  (\\d )\\). ")
    .useRegularExpression(true)
    .matchEntireCell(true)
    .replaceAllWith("$1$2");
}

Note:

If you want to replace 1) to 1, how about the following sample script?

  SpreadsheetApp.getActiveSpreadsheet().createTextFinder("(\\d )\\.. |.  (\\d )\\). |(\\d )\\). ").useRegularExpression(true).matchEntireCell(true).replaceAllWith("$1$2$3");

Sample script:

Note:

References: