It’s important to be able to format your Google Sheets in order to make the data more readable.

But what happens when your text data gets too long to fit inside a cell?

You could increase the width of the cell, but this isn’t always an option.

A better way to deal with long text data is to add wrap formatting to the cell. This way any characters that go past the cell width will appear on a new line inside the cell.

There are 5 ways you can wrap text when working with Google Sheets.

1. Wrap text from the Toolbar.
2. Wrap text from the Format menu.
3. Wrap text manually with a line break.
4. Wrap text by inserting line breaks using a formula.
5. Wrap text by inserting line breaks using Apps Scripts.

This post is going to show you how to do all five! Get the example workbook and follow along.

Wrap Text from the Toolbar

This first method for wrapping text is the quickest and most accessible.

The wrap text format can be applied from the toolbar commands. It just might not be the most obvious as the icon in the toolbar is a bit cryptic.

In this example, you can see the text doesn’t fit entirely in column B entirely and extends into columns C and D. You can fix this by wrapping the text!

Follow these steps to apply the wrap text format.

1. Select the cell or range of cells to which you would like to apply the wrap text format.
2. Click on the Text wrapping icon in the toolbar.
3. Select the Wrap option.

You can see the text now appears on multiple lines and the row height has automatically adjusted so all the text fits.

With wrapped cells, you may also consider merging cells to create a larger cell so the text has room to wrap without changing the row height for other cells.

Wrap Text from the Format Menu

The next method you can use to apply the wrap text format is from the Format menu.

Follow these steps to add wrap text formatting from the Format menu.

1. Select the cell or range of cells to which you would like to add the wrap text format.
2. Go to the Format menu.
3. Select Text wrapping from the menu options.
4. Select Wrap from the options.

Again, this will cause the text to appear on multiple lines within the cell and the row height will automatically adjust to accommodate the text.

Wrap Text Manually with a Line Break

Applying wrap text format will automatically create line breaks in the text based on the cell width and location of space characters in your text.

However, sometimes you need to wrap the text at specific places in the text and you don’t want this to depend on the cell width.

For example, you might always want the street number of an address on its own line like above.

You can do this quite easily in Google Sheets by manually adding a line break inside the cell.

Follow these steps to add a line break to your text.

1. Double left click on the cell or press F2 to edit.
2. Move the cursor to the place in the text where you would like the line break to appear.
3. Hold the Alt key and then press Enter.

This adds the line break at the specified place and the text will always wrap to the next line at that point regardless of the width of the cell.

Wrap Text with a Formula

This is a great option when you want to wrap the text at specific points that are given by a certain character.

For example, you might have an address on one line of text, and parts are separated by a comma but you want these commas to be the line breaks in your wrapped text instead.

Manually replacing all these commas with a line break would be tedious work. Thankfully, this can easily be done using a formula.

= SUBSTITUTE ( B2, ", ", CHAR ( 10 ) )

The above formula uses the SUBSTITUTE and CHAR functions to insert line breaks at each occurrence of a comma and space character.

The CHAR function takes a single number as its sole argument and returns a given ASCII character. Evaluating the CHAR function at 10 will return the line break character.

The SUBSTITUTE function then finds and replaces the comma and space from the original text in cell B2 with the line break character created from the CHAR function.

This will cause the resulting text to wrap at each point where there was a comma and space character.

Wrap Text with Apps Scripts

This is another option for inserting line breaks at specific points to replace any other character in your text data.

You can use Google Apps Scripts to find certain characters in your text and replace them with a line break.

Check out this post for a full breakdown of how to use this code with Apps Scripts.

Click on the Tools tab and select Script editor from the options to open the editor. This is where you can paste the below code.

function findAndReplace() {

  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getActiveRange();
  var data  = range.getValues();
  var ui = SpreadsheetApp.getUi(); 
  var result = ui.prompt("Character(s) to replace with line breaks");
  var button = result.getSelectedButton();

  if (button == ui.Button.OK) {

    var test= true;
    var prevText="";
    var newText="";
    var replacedCount = 0;

    for (var row=0; row<data.length; row++) {
      for (var col=0; col<data[row].length; col++) {
        test = true;
        while (test) {
          prevText = data[row][col];
          newText = data[row][col].replace(result.getResponseText(), String.fromCharCode(10));
          test = (prevText!=newText);
          replacedCount++;
          if (test)
          {
            data[row][col] = newText;
          }
        }
      }
    } 
    range.setValues(data);
    ui.alert(replacedCount + " values found and replaced!");
  }
}

function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var menucols = [
    {name: 'Find and Replace', functionName: 'findAndReplace'}
  ];
  spreadsheet.addMenu('Find', menucols);
}

The above code has two functions.

• findAndReplace prompts a user to input a text string. It will then find and replace this text string with a line break in all cells in the active range.
• onOpen will add a menu item to the Google Sheets interface when you open the the spreadsheet which will allow you to run the findAndReplace function.

Now all you have to do to find and replace commas with line breaks is run the Apps Script from the Find menu.

1. Select the cells in which you want to find and replace with line breaks.
2. Click on the Find menu.
3. Click on Find and Replace.

A pop-up will ask you to input the characters which you want to find. Enter the text such as a comma and space then press the OK button.

The script will run and replace all the occurrences with line breaks. When it’s finished running, a pop-up will show you how many values were replaced.

Remove Wrap Text Formatting

Removing the wrap text formatting is just as easy. This can be done by adding the overflow format to any cells which have had the wrap formatting applied.

Another option is to clear all the formats from the cells. However, you may only want to get rid of the wrapped format.

Remove Wrap Text Format from the Toolbar

Follow these steps to remove the wrap text format from the toolbar.

1. Select the cells to remove the wrap text format you would like to remove.
2. Click on the Text wrapping icon from the toolbar.
3. Select Overflow from the options.

Remove Wrap Text Format from the Format Menu

Follow these steps to remove the wrap text format using the Format menu.

1. Select any cells from which you would like to remove the wrap text format.
2. Click on the Format menu.
3. Select the Text wrapping option.
4. Select the Overflow option from the submenu.

Conclusions

Wrapping text in google sheets is a common problem, but you might be unable to find where this can be done from.

This can be easily done from the toolbar or Format menu.

The issue might be more complex if you require the wrapping to happen at specific points in your text.

Thankfully, it’s possible to set the wrapping points by manually creating line breaks in the data.

This can even be automated via formulas or Apps Scripts to find and replace characters with line breaks.

Do you know any other methods for wrapping text? Let me know in the comments below!