Google Apps Script that takes the value of a cell and separates the last two items by a space moving them to new columns

by

To create a Google Apps Script that takes the value of a cell in Google Sheets, and separates the last two items in the string by a space, moving them to new columns in the same row, follow these steps. This script will loop through a specified range in your sheet, split the content of each cell based on spaces, and then move the last two items to new columns in the same row.

  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 splitAndMove() {
  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 parts = currentCell.split(" ");
    if (parts.length > 2) {
      var lastTwo = parts.slice(-2); // Get the last two elements
      var rest = parts.slice(0, -2).join(" "); // Rejoin the rest
      // Update the original cell with the rest of the string
      sheet.getRange(i + 1, range.getColumn()).setValue(rest);
      // Set the last two elements in the next two columns
      sheet.getRange(i + 1, range.getColumn() + 1).setValue(lastTwo[0]);
      sheet.getRange(i + 1, range.getColumn() + 2).setValue(lastTwo[1]);
    }
  }
}
  1. Modify the var range = sheet.getRange("A1:A10"); line to reflect the actual range you want to process in your sheet. The example "A1:A10" specifies that it will process rows 1 to 10 in column A.
  2. Save the script with a name you will remember.
  3. To run the script, click the play button (▶) next to the splitAndMove function in the Apps Script toolbar.
  4. You might need to authorize the script to run under your Google account. Follow the prompts to give the necessary permissions.

This script assumes that the data to be split is in the first column of the specified range. If your data is in a different column, you will need to adjust the script accordingly. The script splits each cell’s content by spaces, checks if there are more than two words, and if so, moves the last two words to the next two columns, updating the original cell with the remaining part of the string.