Let’s say that you have a string “Top sales person in the Central region” in your data base. You want it to look like the following in a cell in an Excel worksheet:
Top sales person in the Central region
How do you do this?
Splitting text over multiple lines in Excel
In order to display text on multiple lines in a cell in Excel, two conditions must be met:
- The cell must be formatted with “Wrap Text”
- The text must contain the new line character
If you press ALT+ENTER in Excel, Excel automatically formats the cell with “Wrap Text” and inserts a new line character into the cell. But this approach won’t work if you are importing your data from an outside source, for example, if you are importing data with OfficeWriter’s ExcelTemplate object.
Formatting the cell with “Wrap Text” is as easy as right clicking the cell, going to Format Cell > Alignment and checking off ‘Wrap Text’. The next question is how to get the new line character into the cell.
Option 1: Use an Excel formula to concatenate the new line character to the text in the cell
In the example, we need to append “Highlights:” and the new character line to the text that’s already there. Let’s say that the text is in cell D8. Then the formula would be
=CONCATENATE("Highlights", CHAR(10), D8). If the formula had to be applied to a series of cells, where you weren’t sure if there would be a comment or not, then you could wrap that formula in an IF formula:
=IF(LEN(D8)>0, CONCATENATE("Highlights:", CHAR(10), D8), ").
What if the text from the database needed to be split over multiple lines?
Let’s suppose the text in the database already contained “Highlights”: “Highlights: Top sales person in the Central region”. Then how do you break apart the strings?
First we need to grab the “Highlights:” part. We can employ Excel’s LEFT(N_chars) function, which grabs the N left-most characters:
=LEFT(D8, 11) will return “Highlights:”
Next we need to grab just the second part. We can use Excel’s MID(text, start_index, chars) function to get a specific sub-string:
=MID(D8, 13, LEN(D8)-11) will return “Top sales person in the Central region”.
We can concatenate these together with the new line character:
=CONCATENATE(LEFT(D8,11), CHAR(10), MID(D8, 13, LEN(D8)-11)). We can also wrap it in the similar IF formula if we only wanted to apply this formula if there was a comment. (Otherwise you will end up with #VALUE!).
Option 2: Add the new line character to the text that it’s imported into the file
(For example, manipulating the data in .NET code before importing it into a file using ExcelTemplate).
Just add the newline character to your text: “Top sales person in the Central region” –> “Highlights: /n Top sales person in the Central region”. When the text is imported, Excel will respect the new line character. Make sure that the cell is formatted with “Wrap Text” ahead of time.
- Special Characters in Excel: http://www.cpearson.com/excel/chars.htm
- Using Excel’s Find and Mid to extract a substring when you don’t know the start point
- Save time by using Excel’s Left, Right, and Mid string functions