You are currently browsing articles tagged Excel.

My intern came to me today and asked about displaying multiple lines within a cell.  I’ll write the first real post in his honor.

Most people know that when entering text in Excel, pressing ALT+ENTER makes a new line.  However, he needed to generate this multi-line string dynamically (via a formula).  The new line characters, carriage return (CR) and line feed (LF), are not as obvious in Excel as most programming languages make them.

Note: This refers to the ‘\r\n’ in C-styled languages and vbCrLf in Visual Basic. In many environments, however, only the latter (‘\n’ and vbLf) is necessary to create a new line.

Download the Sample File

One way to do this, which may be more intuitive to non-programmers, is to use the same ALT+ENTER key stroke by entering this formula:

= [first line] & “[press ALT+ENTER here]” & [second line]…

Another way to do this, which is longer but probably preferred by programmers, is to employ the new line characters (CR + LF):

= [first line] & CHAR(13) & CHAR(10) & [second line]…

Similar to aforementioned note, only CHAR(10) is necessary for the desired results.  Add CHAR(13) only if you have the pet peeve from certain programming styles.

This entry may sound obvious, but here’s the catch: by using the formula above, you probably will still see all your text concatenated on one line.  You still need to wrap text (Format -> Cells -> Alignment tab -> Wrap text) in order to see multiple lines!  Note: Excel automatically sets the wrap text property when you enter plain text (instead of a formula) with a line break in a cell.

Download the Sample File

Tags: , , , ,