Excel Tips and Tricks #458 - Revisiting Line breaks in Excel

Wrap text

Normally, Excel cells display text that is longer than the cell by overwriting on any blank cells to the right. This continues for as long as necessary:

Of course, the edge of the window can also cut off long text.

If we use the option wrap text from the ‘Home’ menu, then Excel will break the text up into the relevant number of lines to show it all:

As noted in the image, the Wrap Text button will automatically expand the row height to show all the text; however subsequent changes to the column width or row height don’t automatically adjust the cell size to display all the text.

Adding line breaks

Ordinarily, line breaks are inserted by Excel according to the cell size. But we can add them manually with a line break, created by pressing Alt and Enter:

You can also create a line break in a CONCAT or other text formula using the CHAR function – CHAR(10) is the line break character but may not be sufficient on its own. CHAR(10) moves the cursor down 1 line while CHAR(13) is a carriage return which forces the cursor back to the left of the line. It is best practice to use both CHAR(10) and CHAR(13) in combination. You must ensure you have enabled Wrap Text on the cell where this formula is used to enable the line breaks:

We cover CHAR along with CODE, UNICODE and UNICHAR in Tip 213.