How To Wrap Text In Google Sheets

In this post, we’ll look at how to wrap text in Google Sheets so that long strings fit inside cells and can be read easily.

Method 1: Via Format Menu

Select a range of data and go to the menu: Format > Wrapping > Wrap

Wrap Text In Google Sheets Format Menu

Next to the wrap text option, you’ll find the clip option (show on one line and don’t allow any overflow) and overflow option (show on one line and allow to spill into adjacent cells).

Method 2: Wrap Text Via Toolbar

Select a range of data and then use the wrap text button in the toolbar:

Wrap Text Toolbar in Google Sheets

As with the Format menu, you’ll find the Clip and Overflow options next to the wrap text option.

After wrapping, your data will look like this:

Data After Wrap Text

How To Wrap Text In Google Sheets Manually

Use Ctrl + Enter to create a new line in a cell:

Manually Wrap Text in Google Sheets

This can also be used inside the formula bar to create multi-line formulas.

How To Create A Shortcut Key To Wrap Text

There is no built-in shortcut key to wrap text in Google Sheets but we can create a custom one using Google Sheets macros.

Select the data range, then go to the menu: Extensions > Macros > Record macro

Make sure you choose “Use relative references”.

Wrap the text using one of the methods above.

Finally, assign a shortcut key number to your macro.

On a Mac, the shortcut key will be of the form: ⌘ + ⌥ + Shift + 1

And on a PC or Chromebook, the shortcut will be of the form: Ctrl + Alt + Shift + 1

Here’s how to create a macro to wrap text in Google Sheets:

Create Wrap Text Shortcut Key In Google Sheets

Now you can use this shortcut key to wrap text in any ranges you select.

Note macros only work in the Google Sheet you create them in, so it’s only worth doing if you envisage wrapping text often.

How To Wrap Text In Google Sheets Using Apps Script

Google Apps Script is a scripting language for extending the functionality of Google Sheets (and other Google Workspace tools).

There are four get methods and four set methods available in Apps Script that let you confirm whether the text is wrapped or not, and turn it on or off.

getWrap and setWrap

getWrap() tells you whether the text in the cell is wrapped.

setWrap() sets the cell wrap for a single cell.

For example, this code will wrap text in cell A1:

function wrapTest() {
  const sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange("A1").setWrap(true);
}

getWraps and setWraps

getWraps() tells you whether the cells in a range are wrapped or not.

setWraps() applies a rectangular grid of word wrap policies to a range.

We need to use the double-array notation to denote the 2-D structure of the range.

The outer array […] denotes the range and each inner […] creates a row. Finally, the commas separate the columns within the row.

So [[true,false,true,false]] will work for a single row with 4 columns, for example A1:D1, as in this example:

function wrapTest() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const wraps = [[true,false,true,false]];
  sheet.getRange("A1:D1").setWraps(wraps);
}

Whereas this [[true],[false],[true],[false]] denotes a single column and 4 rows, for example A1:A4, as in this example:

function wrapTest() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const wraps = [
    [true],
    [false],
    [true],
    [false]];
  sheet.getRange("A1:A4").setWraps(wraps);
}

getWrapStrategy and setWrapStrategy

getWrapStrategy() returns the text wrapping strategy of the top-left cell of the range.

setWrapStrategy() sets the wrap rules for the cells in the range.

This code:

function wrapTest() {
  const sheet = SpreadsheetApp.getActiveSheet();
  Logger.log(sheet.getActiveCell().getWrapStrategy());
}

outputs the wrapping strategy of the active cell in the logs.

The result is either WRAP, OVERFLOW, or CLIP, known as the enumeration of the wrapping strategy.

getWrapStrategies and setWrapStrategies

getWrapStrategies() returns the text wrapping strategies of cells in a range.

setWrapStrategies() sets a rectangular grid of wrap strategies for a range.

For example, this code sets A1 to be clipped, A2 to be wrapped, A3 to be clipped, and A4 to overflow:

function wrapTest() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const wraps = [
    [SpreadsheetApp.WrapStrategy.CLIP],
    [SpreadsheetApp.WrapStrategy.WRAP],
    [SpreadsheetApp.WrapStrategy.CLIP],
    [SpreadsheetApp.WrapStrategy.OVERFLOW]
  ]
  sheet.getRange("A1:A4").setWrapStrategies(wraps);
}

And there you have it!

Lots of different methods to wrap text in Google Sheets.

Leave a Reply

Your email address will not be published.