Excel Tip: Multiple lines of text in one cell

Before we get into this week's post, just a quick reminder that you can still get 20% off Mynda Treacy's Excel Dashboards course until Thursday, so don't dither any longer if you want to partake!

This week I want to talk about entering multiple lines of text in one cell.

I'm not talking about wrapping text which simply runs the text over multiple lines when it doesn't fit in the width of the cell (this can be enabled for any cell by selecting the cell and clicking Wrap Text on the Home ribbon). I'm talking about choosing where the line breaks are to show different bits of information on different lines (in the same cell), or to break longer text into paragraphs,

This can be particularly useful in mail-merge data, for the body of an email, for example.

There are two different scenarios where you may want to do this which need addressing slightly differently.

The first, and simplest, scenario is when you are entering the text directly into the cell.

In this case, you can simple press Alt-Enter whenever you need a line break (in the same way that you might hit Enter in a Word document). When you do this, notice that Excel has automatically switched on Wrap Text for the cell (Wrap Text on the Home ribbon will be highlighted when the cell is selected).

The second scenario is when you are generating the text using a formula. Here you need to refer to the line feed character by its code.

We can use the CHAR function to refer to a character by its character code,

CHAR(10) is the Line Feed character.

We can build up the text in our cell using the & character to join the text together.

So, say that we have the text for paragraph 1 in cell A1, and the text for paragraph 2 in cell B1, then we can join them together using & as follows:

=A1&B1

However, the text from both cells will simply by joined together, without even a space to separate them, never mind a line break.

However, we can insert the Line Feed character as follows:

=A1&CHAR(10)&B1

At first this might appear to look exactly the same, however the line feed is there and will be used in a mail merge, for example.

It will have no effect on how it is displayed in Excel, until we switch Wrap Text on for the cell (it is not automatic in this case). We can now see that a line break has been forced where we put CHAR(10).

You can use this to create quite complicated sections of text, built up from multiple paragraphs selected using IF statements and or lookups.



If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".