Transform multiple-line cell contents into columns in Excel

Mar 24th 2010
Share this content
Spreadsheets and graphs on a desk
xfgiro/istock

Sometimes in Excel you may encounter a situation where each cell contains multiple lines of data. It's not a huge task to manually type a few records, but what if you have dozens or hundreds of records in this format? In this article I'll explain some simple techniques that can make quick work of transforming this data into columns.

The first step in the process is to use the SUBSTITUTE function to replace the non-printing new line character with another character. You must use a formula, because Excel's Find and Replace feature doesn't allow you to replace non-printing characters such as new lines. In a pinch, you can use Word to carry out such replacements but it's easy to use SUBSTITUTE.

The SUBSTITUTE function has four arguments:

  • text – In this case, text will be a cell that contains new line characters
  • old_text - Excel's CHAR function can generate the non-printable new line character
  • new_text – I like to use the | symbol (often referred to as the pipe symbol) as a unique identifier in place of the new line character. This symbol usually appears above the \ key on your keyboard.
  • instance_num – This is an optional argument that I'll omit in this case because I want to replace all of the new line characters with the pipe symbol. If I put a number here, only that quantity of new line characters would be replaced.

Excel's CHAR function just has a single argument wherein you indicate the number of the character that you want to return. New line characters are number 10, so I'll use CHAR(10) to indicate a new line. Here is a chart of all of the character symbols.

Now I'm ready to enter the formula into cell B1. Click on cell B1, and then double-click the Fill Handle in the lower right-hand corner of the cell to copy the formula down through cell B5. The SUBSTITUTE function takes the data from its original display in multiple rows and puts it into a single row, with a | symbol in between data that was on each row.

Now select cells B1 through B5, and then press Ctrl-C to copy the range to the clipboard. Right-click on cell B1, choose Paste Special, and then Values. Leave cells B1 through B5 selected, and choose Data, and then Text to Columns.

Choose Delimited, and then click Next. Then choose Other, and enter the | symbol. You can clear the checkbox for Tab, or leave it clicked – this won't have any impact if your data doesn't contain tab characters. Click Finish to complete the wizard steps. The data is now transformed into columns

Replies (21)

Please login or register to join the discussion.

avatar
By anon
Jun 26th 2015 01:10

thanks for that!

Thanks (0)
avatar
By Noé
Jun 26th 2015 01:10

Thanks it is very nice it is all I needed ;)

Thanks (0)
avatar
By Brendan Hampshire
Jun 26th 2015 01:11

Copy and paste special as text (not unicode text)..?

Thanks (0)
avatar
By Bokhari
Jun 26th 2015 01:11

Thanks for the tips. And want to know how to transform from multiple columns/row into multiple-line cell contest?
Thanks

Thanks (0)
Replying to Nell Nichols:
avatar
By Aso
Jun 26th 2015 01:11

I have the same question...

Thanks (0)
Replying to Gevariya Nikunj:
avatar
By David Ringstrom
Jun 26th 2015 01:11

Bokhari and Aso,

Just noticed your questions. I'll write up this technique soon as a new article that I'll link to here, but in the short answer is if you want to combine cells A1, B1, and C1 into a single multi-line cell, say D1, use this formula:

=A1&CHAR(10)&B1&CHAR(10)&C1

then turn on Wrap Text for cell D1. Char(10) derives a carriage return, while the ampersand can be used in place of Excel's CONCATENATE function.

David

Thanks (0)
avatar
By Kay
Jun 26th 2015 01:11

Hi ,
Is there any formula to convert the pipe delimited file to columns automatically instead of doing it manually..

Thanks (0)
Replying to Mike:
avatar
By David Ringstrom
Jun 26th 2015 01:11

Kay,

It's possible to do by crafting formulas that use the FIND function to locate the positions of the pipe symbol, and then use LEFT, MID, or RIGHT to extract the contents, but the formulas get very involved, very quickly. There is an alternate technique, however, that involves using a data feature in Excel to establish a link to the text file that preserves the parsing settings used by the Text to Columns wizard. I'll write this technique up this week and post a link back to the new article. This technique would allow you to simply choose a new file name and the contents would automatically transform into columns.

David

Thanks (0)
Replying to Ed:
avatar
By David Ringstrom
Jun 26th 2015 01:11

Kay,

This article explains how to automate separating data into columns. Please feel free to let me know if you have any follow-up questions:

https://www.accountingweb.com/a...

David

Thanks (0)
avatar
By none
Jun 26th 2015 01:11

there is no formula shown in figure 2... this article is useless... very poorly written in terms of step by steps

Thanks (0)
avatar
By Gary Beggs
Jun 26th 2015 01:11

Saved my life - 800 line database with three to five lines of name/address info in single cells. Your method worked like a charm. Disagree with commenter below. Article was very well written and should be executable by a moderate to proficient level user

Thanks (0)
avatar
By monkeyflasher
Jun 26th 2015 01:11

This was incredibly helpful. It saved me hours of editing. THANK YOU!

Thanks (0)
avatar
By LearningExcel
Jun 26th 2015 01:11

this is a really cool suggestion! I have been trying to solve a similar problem for quite some time, because I did not realize that I should go around non-printing characters. This suggestion have worked right away on my data, and helped me to save hours!

Thanks (0)
avatar
By Anand Padhye
Jun 26th 2015 01:11

Thank you so much for the info. this has proved to be such a timesaver for me. Great tip

Thanks (0)
avatar
By guest
Jun 26th 2015 01:11

Thank you. Thank you. Thank you.

Thanks (0)
avatar
By Jon
Jun 26th 2015 01:11

I never usually leave comments but this is awesome. Thank you!

Thanks (0)
avatar
By Sandeep Parashar
Jun 26th 2015 01:11

Thanks for this type of service it is like teacher for me. So thanks once again.

Thanks (0)
avatar
By TzantzaruBzzz
Jun 26th 2015 01:11

Thanks a lot! This post is exactly what i needed. Glad that I've discovered it

Thanks (0)
avatar
By Sagarika
Jun 26th 2015 01:12

Thank you. Huge help. Saved me the trouble of fresh downloads

Thanks (0)
avatar
By Andrew
Jun 26th 2015 01:12

Try this (re-posting from another site from a user called Nick)
Step 1) select the column
Step 2) go into text to column
Step 3) tick other and in the box type 0010 while Holding the Alt key. (it looks as if youve typed nothing, dont worry)
Step 4) hit next and finish

The reason this works is that 0010 is the ASCII code for a line break. (note that the numbers must be typed on a numberpad).
You can also do a find and replace for Alt-0010 and then do the text to column.
Hope that helps.

Thanks (0)
avatar
By lindy100
Mar 24th 2016 05:10

I have a twist on this... I need to pull the complete text, with new lines from one workbook into another. It must be formatted exactly the same way in the destination cell. Setting one cell '=' to another strips out the new lines. Is this possible. Many thanks!

Thanks (0)