Google Apps Script - How to extract the letters from a string containing letters and numbers?

user_19235694 0
Viewed 181

I have a column with strings containing a name and an ID concatenated. How can I extract just the name part of the string and use that for filtering a dataset and copying it over to another worksheet in the google sheets file using the google apps script? The main thing I'm having difficulty with is just extracting the names from the column since they can vary in length. Additionally, I'm trying to perform automation using google apps script, so I want to avoid making a column manually and use something like regex.

Below is an example of how the column values look:

Identity
Jane100
Adam500
Adam500
Erica234
Jessica8
google-apps-script google-sheets
2 Answers

We can use regular expressions here:

var input = "Adam500";
var regExp = new RegExp("([a-z]+)", "i")
var name = regExp.exec(input)[1];
Logger.log(name);  // Adam
user_18632290

I believe your goal is as follows.

  • You want to copy the following values on a column of the source sheet.

      Identity
  Jane100
  Adam500
  Adam500
  Erica234
  Jessica8

  • And, you want to paste the following values to a column of the destination sheet (in the same Spreadsheet) by converting from the above values.

      Identity
  Jane
  Adam
  Adam
  Erica
  Jessica

  • You want to achieve this using Google Apps Script.

In this case, how about the following sample script?

Sample script 1:

function sample1() {
  const srcSheetName = "Sheet1"; // Please set the source sheet name.
  const dstSheetName = "Sheet2"; // Please set the destination sheet name.
  const srcColumn = 1; // Values are retrieved from column "A".
  const dstColumn = 1; // Values are put to the column "A".

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [srcSheet, dstSheet] = [srcSheetName, dstSheetName].map(e => ss.getSheetByName(e));
  const srcValues = srcSheet.getRange(1, srcColumn, srcSheet.getLastRow()).getValues();
  const dstValues = srcValues.map(([a], i) => [i == 0 || !a.toString() ? a : a.replace(/\d+/g, "")]);
  dstSheet.getRange(1, dstColumn, dstValues.length).setValues(dstValues);
}
  • In this sample script, the values are retrieved from the column of the source sheet, and the converted values are put on the destination sheet.

Sample script 2:

function sample2() {
  const srcSheetName = "Sheet1"; // Please set the source sheet name.
  const dstSheetName = "Sheet2"; // Please set the destination sheet name.
  const srcColumn = 1; // Values are retrieved from column "A".
  const dstColumn = 1; // Values are put to the column "A".

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [srcSheet, dstSheet] = [srcSheetName, dstSheetName].map(e => ss.getSheetByName(e));
  const last = srcSheet.getLastRow();
  const dstRange = dstSheet.getRange(1, dstColumn);
  srcSheet.getRange(1, srcColumn, last).copyTo(dstRange);
  dstRange.offset(1, 0, last - 1).createTextFinder("\\d+").useRegularExpression(true).replaceAllWith("");
}
  • In this sample script, the values are copied from the column of the source sheet to the destination sheet. And, the copied values are converted. In this case, the text styles can be copied.

References:

user_71086530
Related