2

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.

1 Answer 1

3

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.

7
  • 1
    Hear hear for Google Apps Script.....and Jerry101 !!
    – Jacob Jan
    Apr 17, 2015 at 8:33
  • I added the section "Explanation and How to learn this stuff" to make this more generally instructive.
    – Jerry101
    Apr 17, 2015 at 22:50
  • Thanks. Is it possible to make the line break runs while you actively edit the cell. Whenever you type "a.", then a line break will be created instead of running the code after you finish typing Mar 1, 2021 at 3:48
  • 1
    @HassanAlmaateeq I don't think a script can run while you're actively editing a cell, but you can write an onEdit(e) function that runs when a user changes a value in a spreadsheet. That function can apply InsertLineBreaksInString() to the newly-edited cell.
    – Jerry101
    Mar 1, 2021 at 7:55
  • 1
    @hassanalmaateeq to replace each pair of spaces with a newline, use str.replace(/ /g, '\n'). Or maybe you want to replace each sequence of two or more spaces? Read about "javascript regular expressions". There are lots of tutorials and articles about it for readers with different backgrounds. Experiment with them, and if you get stuck, do ask a new question about that.
    – Jerry101
    Mar 1, 2021 at 16:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.