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.

3 thoughts on “Breaking text to two lines

  1. Jayson,

    I'm curious how you mean? Are you looking to break your VBA onto two lines for readability? In that case, no... you'd use the following instead:
    & _
    (Ampersand + space + underscore)

    If you're looking two insert a hard return into a text string for a messagebox, I'd use
    "text1" & vbNewLine & "text2"

  2. Yeah, I wasn't very clear. Can you write to a text box on a form with a similar result?

    you mentioned vbNewLine. What's the difference between that and vbCrLf? I can't seem to find any explanations online.

If you have a comment or question about the blog post content, please feel free to post it here. If you need help adapting this solution to your own needs, please post in our free help forum.

Your email address will not be published. Required fields are marked *