Google-sheets – How to add line breaks in a cell before specific keywords in Google Sheets

google sheetsgoogle-apps-script

I'm new to Google Sheets and wondering how to tell GS to insert line breaks in a cell with text if there are some one or two letter before the text.

For instance, I have a cell like this:

enter image description here

I want to tell the script to create one paragraph for each choice:

enter image description here

It this case, the code must understand that the line breaks has to be before the pattern "[a,b,c,d, …]."

Important: each paragraph has to be in the same cell. Plus, on the spreadsheet there will be many cells (in the same column) so the code has to iterate the process for each cell.

Best Answer

This script will do the trick:

/**
 * Replaces some spaces with newlines in str, specifically, ' a.' becomes '\na.' for any letter 'a'.
 * See http://webapps.stackexchange.com/questions/76035/how-to-add-line-breaks-in-a-cell-before-specific-keywords-in-google-sheets
 */
function InsertLineBreaksInString(str) {
  return typeof(str) === 'string' ? str.replace(/ (\w\.)/g, '\n$1') : str;
}

/** Replaces some spaces with newlines in all the cells in the given range. */
function InsertLineBreaksInCells(range) {
  var vals = range.getValues();

  for (var i in vals) {
    var row = vals[i];
    for (var j in row) {
      row[j] = InsertLineBreaksInString(row[j]);
    }
  }

  range.setValues(vals);
}

/** Replaces some spaces with newlines in all the cells in the current Sheet's active range. */
function InsertLineBreaks() {
  var range = SpreadsheetApp.getActiveRange();
  InsertLineBreaksInCells(range);
}

/** Adds a menu to the Google Sheet. */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
      .addItem('Insert Line Breaks', 'InsertLineBreaks')
      .addToUi();
}

To set it up:

  1. In Google Sheets, invoke Tools > Script Editor...
  2. Paste in this script.
  3. Save the script.
  4. Go back to the Sheet.
  5. Reload the browser page.
  6. Confirm the requests for access permissions.

To use it:

  1. Select the cells you want to process.
  2. Invoke the menu command Custom Menu > Insert Line Breaks.

Explanation and How to learn this stuff

The first part, function InsertLineBreaksInString(str), processes a string by replacing some spaces with newline characters. If the argument is a string, it uses a "regular expression" to look for the pattern SPACE LETTER PERIOD and replace it with NEWLINE LETTER PERIOD. Regular expressions are handy for lots of string-processing work.

This is a matter of JavaScript programming and regular expressions. There are many tutorials on these topics including Khan Academy classes, Codecademy classes, and more. You can experiment right in your browser's JavaScript Console and in online interactive tutorials like Learn JavaScript.

The second part, function InsertLineBreaksInCells(range), uses the first function to process all the cell values in a spreadsheet range. See Google's Quickstart: Macros, Menus, and Custom Functions and Spreadsheet Service reference. Within Google Sheets, you can invoke the menu command Tools > Script Editor... to get to the script editor, and from there to the Help info.

The third part, function InsertLineBreaks(), get the currently selected range in the current sheet, and calls the second function. See the same documentation for this.

The last part, function onOpen(), adds a menu to the Google Sheet so you can invoke your script. This is documented in the Base Service part of the Apps Script reference, with examples, and there's also an example in the Quickstart mentioned above.

Related Topic