Google Apps Script cells string value separates it by the first comma then pastes the remaining part of the string into the following column

by

To create a Google Apps Script for Google Sheets that processes a range of cells by taking each cell’s string value, separates it by the first comma found in the string, and then pastes the remaining part of the string into the following column, follow these steps. This script will iterate over a specified range, split each cell’s content at the first comma, and move the part of the string after the comma to the next column.

  1. Open your Google Sheets document.
  2. Click on “Extensions” > “Apps Script”.
  3. Delete any code in the script editor and paste the following script:
function splitByFirstCommaAndMove() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  // Specify the range you want to process, e.g., "A1:A10"
  var range = sheet.getRange("A1:A10");
  var values = range.getValues();

  for (var i = 0; i < values.length; i++) {
    var currentCell = values[i][0]; // Assumes the data is in the first column
    var firstCommaIndex = currentCell.indexOf(",");
    if (firstCommaIndex !== -1) {
      var beforeComma = currentCell.substring(0, firstCommaIndex);
      var afterComma = currentCell.substring(firstCommaIndex + 1);
      // Update the original cell with the part before the comma
      sheet.getRange(i + 1, range.getColumn()).setValue(beforeComma.trim());
      // Paste the part after the comma into the following column
      sheet.getRange(i + 1, range.getColumn() + 1).setValue(afterComma.trim());
    }
  }
}
  1. Adjust the var range = sheet.getRange("A1:A10"); line to specify the actual range you want to process in your sheet. The example "A1:A10" indicates it will process rows 1 to 10 in column A.
  2. Save the script with a memorable name.
  3. To run the script, click the play button (▶) next to the splitByFirstCommaAndMove function in the Apps Script toolbar.
  4. The first time you run the script, you’ll need to authorize it to run under your Google account. Follow the on-screen prompts to give the necessary permissions.

This script takes the string value of each cell in the specified range, finds the first comma, and splits the string at that point. It then updates the original cell with the substring before the comma and places the substring after the comma into the next column, ensuring both parts are trimmed of any leading or trailing whitespace. This operation is performed on each cell in the range sequentially.