How to import text on multiple lines in Excel

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:

Highlights:
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.

Additional reading:

Everything You Need to Know About OfficeWriter: The Whitepaper

How to Render an SSRS Report Asynchronously using the Async\Await Keywords in C# 5.0 with .NET 4.5

March Madness: Using Excel to Pick the Upset

2 thoughts on “How to import text on multiple lines in Excel”

How would you go the other way….
Multiple lines in Word imported into just one row in Excel?

Thanks much,

Hi SCKIM,

If you have text that contains the new line character, but you want the text to display on a single line in Excel, you will need to search for the new line characters and replace them with empty strings.

For example:

In cell A1, there is formula =CONCATENATE(“Highlights: “, CHAR(10), “Top sales person in Central region”)

Use the REPLACE() formula to perform the search and replace. REPLACE() takes 4 arguments: the text to search, the starting index, the number of characters to include in the text to be replaced, and the new text.

We can call REPLACE() to search in A1, starting at the index where the new line character, CHAR(10), appears, set the text to be replaced to one character, and replace it with an empty string.

=REPLACE(A1, FIND(CHAR(10), A1), 1, “”)

The text should be on a single line.

You can also do a similar search and replace in .NET code.

Does this answer your question?

Comments are closed.

SoftArtisans