I’ve been using Google Spreadsheets to clean data and I recently discovered this little script to trim (remove leading and trailing spaces) from all cells. This is really great.

In an open Google Spreadsheet:

1. Go to Tools, then Script Editor.
2. Copy and paste the code below into the Script Editor.
3. Save the script.
4. In the Script Editor go to Run, then onOpen.
5. Return to the open Spreadsheet and you’ll see a new menu item, Trim Cells (you can close the Script Editor).
6. Highlight the cells you’d like to trim and run your new script!

The Trim Cell Script

var ss = SpreadsheetApp.getActiveSpreadsheet();

function trimSpacesInSelectedCells() {
  var sheet = ss.getActiveSheet();
  var activeRange = sheet.getActiveRange();
  for (var cellRow = 1; cellRow <= activeRange.getHeight(); cellRow++) {
    for (var cellColumn = 1; cellColumn <= activeRange.getWidth(); cellColumn++) {
      cell = activeRange.getCell(cellRow, cellColumn);
      cellFormula = cell.getFormula();
      if (cellFormula[0] != "=") {
        cellValue = cell.getValue();
        cell.setValue(String(cellValue).trim());
      }
    }
  }
}

function onOpen() {
  var entries = [{
    name : "Remove leading and trailing spaces",
    functionName : "trimSpacesInSelectedCells"
  }];
  ss.addMenu("Trim Cells", entries);
}