5 Ways to Wrap Text in Google Sheets [Illustrated Guide] (2023)

It's important to be able to format your Google spreadsheets to make the data more readable.

But what if the text data is too long to fit in one cell?

You can increase the cell width, but that's not always an option.

A better way to handle long text data is to add a newline format to the cell. This way, any characters that exceed the width of the cell will appear on a new line within the cell.

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

1. Wrap text fromtoolbar.
2. Wrap text fromFormatMenu.
3. Wrap the text manually with anew line.
4. Line break by inserting line breaks with aFormula.
5. Wrap text by inserting line breaks withapplication scripts.

This post will show you how to make all five! Get the sample workbook and run through it.

Wrap toolbar text

This first text wrapping method is the fastest and least expensive.

Text wrapping formatting can be applied via toolbar commands. It might not be the most obvious as the toolbar icon is a bit gimmicky.

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

Follow these steps to apply wrapping text formatting.

1. choosethe cell or range of cells to which you want to apply text wrapping formatting.
2. click noText matchingToolbar icon.
3. ChooseenvelopePossibility.

You can see that the text is now displayed in multiple lines and theRow height was automatically adjustedThen the whole text fits.

For encapsulated cells you might also consider thisCombine cells to create a larger cellThis gives the text room to wrap without changing the row height of other cells.

Line break in the Format menu

The next method you can use to apply text wrapping formatting is fromFormatMenu.

Follow these steps to add wrapping text formatting fromFormatMenu.

1. choosethe cell or range of cells that you want to add text wrapping formatting to.
2. go toFormatMenu.
3. chooseText matchingthe menu options.
4. chooseenvelopeof options.

Again, this causes the text to appear on multiple lines within the cell and theThe row height is adjusted automaticallyrecord the text.

Manually wrap text with line breaks

Applying wrapping text formatting automatically creates line breaks in the text based on the cell width and the position of spaces in your text.

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

For example, you may always want the house number of an address on its own line, as shown above.

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

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

1. Double-click with the left mouse buttonin the cell or pressF2Edit.
2. Move the cursor to the position in the text where you want the line break to appear.
3. shutAlternativebutton and then pressEnter.

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

Wrap text with a formula

This is a great option when you want to wrap text at specific locations given by a specific character.

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

Manually replacing all those commas with a newline would be a tedious job. Luckily, this is easy to do with a formula.

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

The formula above uses theERSATZjCHARACTERSfunctions forInsert line breaksat each occurrence of a comma and a space.

IsCHARACTERSThe function takes a single number as its only argument and returns a specific ASCII character. evaluation ofCHARACTERSfunction on10returns the newline character.

IsERSATZThe function finds and replaces the original text comma and space in the cellB2with the newline character created from theCHARACTERSFunction.

This will wrap the resulting text wherever there was a comma and space.

Wrap text with application scripts

This is another option to insert line breaks at specific points to replace other characters in your text data.

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

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

click noTooltap and selectScript Editorin the options to open the editor. Here you can paste the following code.

function findAndReplace() { var sheet = SpreadsheetApp.getActiveSheet(); var range = sheet.getActiveRange(); var data = range.getValues(); var ui = SpreadsheetApp.getUi(); var result = ui.prompt("Characters to replace with newlines"); Button var = result.getSelectedButton(); if (button == ui.Button.OK) {var test= true; var prevText=""; var newText=""; var CounterSubstitution = 0; for (var line=0; line<data.length; line++) { for (var col=0; col<data[line].length; col++) { test = true; while (test) { pretext = data[row][column]; newText = data[line][col].replace(result.getResponseText(), String.fromCharCode(10)); test = (old text! = new text); replace counter++; if (test) { data[row][column] = newText; } } } } range.setValues ​​(data); ui.alert(replacedCount + "Values ​​found and replaced!"); } } function onOpen() { var Spreadsheet = SpreadsheetApp.getActive(); var menucols = [ {name: 'find and replace', function name: 'find and replace'}]; worksheet.addMenu('Search', menucols);}

The code above has two functions.

• search and replaceprompts the user to enter a text string. It then finds that text string and replaces it with a newline in all cells in the active range.
• openadds a menu item to the Google Sheets UI when you open the spreadsheet that lets you run thesearch and replaceFunction.

Now all you have to do is run the application script to find commas and replace them with newlinesMeetMenu.

1. choosethe cells you want to find and replace with line breaks.
2. click noMeetMenu.
3. click insearch and replace.

A pop-up window will ask you to enter the characters you want to find. Enter text, e.g. B. a comma and a space and press the keyACCORDINGLYTaste.

The script runs and replaces all occurrences with newlines. When it's done, a popup will show how many values ​​have been replaced.

Do not format wrapping text

Removing wrapping text formatting is just as easy. This can be done by adding the overflow format to any cell that has the break format applied.

Another option isClear all cell formats. However, you may just want to get rid of the bundled format.

Remove wrapping text formatting from the toolbar

Follow these steps to remove wrapping text formatting from the toolbar.

1. choosethe cells to remove the text wrapping formatting that you want to remove.
2. click noText matchingtoolbar icon.
3. chooseoverflowof options.

Remove the text wrap format from the Format menu

Follow these steps to remove wrapping text formatting using the Format menu.

1. chooseeach cell from which you want to remove the wrapping text formatting.
2. click noFormatMenu.
3. ChooseText matchingPossibility.
4. Chooseoverflowsubmenu option.

Conclusions

Text wrapping in Google Sheets is a common problem, but you may not be able to figure out where it's possible.

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

The problem can become more complex if you want the break to occur at specific points in your text.

Fortunately, it is possible to define the fit points by manually creating line breaks in the data.

This can even be automated via formulas or application scripts to find characters and replace them with newlines.

Do you know other methods to wrap text? Let me know below in the comments!