Breaking text to two lines

I was working on my budget package today, and decided that I wanted to break the text so that it showed on two lines. For many novice users this means that they put one or two words in one cell, then the remaining text in the cell below. Personally I try to avoid that, as I find it unnecessary.

So here's what my output might look like:

I sized the column for illustration only, and would normally have it much narrower.

It's true that you could accomplish this by checking the Wrap Text box (Format CellsàAlignment), but that only works if the column is at the right width to have the wrap where you want it. That's not always good enough for me when I'm sizing my columns to hold the required numbers, and my headers might look all messy. By controlling where the text breaks, I can get it to look exactly how I want.

So how did I do this? After all, when you press Enter the data is committed to the cell and you move down a row.

The answer is to press ALT+ENTER together. The text will then break exactly where your cursor is.

Now that's all good, but what about when you are filling the cell contents with a formula? Let's look at the following example:

Have a look at the contents of C1. In this case it is actually driven by the following formula:

=YEAR(B5) & " Budget vs " & YEAR(B4) & " Projected Actuals"

Now this is all good, but I'd like to force the 2009 to be on the second line. I can't go in and type an ALT+ENTER in this case. Actually, I can but all it does is split my formula to show on two lines in the formula bar like this:

While that's really helpful for auditing formulas, it doesn't achieve the desired effect. (I believe that the option to expand the formula bar was added in Excel 2007 – just left click and drag down when your mouse turns into the up and down arrows on its bottom border – but in previous versions you'd have to edit the cell and use your arrow keys to see the different lines.)

The secret to breaking the text in a formula is to use the CHAR function to enter a non-printing character; a hard return. In this case CHAR(10) will do the job quite nicely. So I'd edit the formula to read as follows:

=YEAR(B5) & " Budget vs " & CHAR(10) & YEAR(B4) &" Projected Actuals"

And it would get me the following result:

Just a quick note as well… I use the & sign to join text instead of the horrible CONCATENATE function. It's not required to have spaces each side of the & character, but it doesn't hurt either. I normally wouldn't put them in, but I did it to make the formula a bit more readable.

And for the record, this should work as far back as at least Excel 97, if not further.