Transform multiple-line cell contents into columns in Excel

By David H. Ringstrom, CPA

Sometimes in Excel you may encounter a situation such as that shown in Figure 1, 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.
 
Figure 1: It can be difficult to convert multiple lines of text in a single cell into columns
 
Expert Tip: If you want to recreate data similar to what you see in Figure 1, type a line of text in a cell, and then press Alt-Enter to add a new line.
 
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, as shown in Figure 2, but it's easy to use SUBSTITUTE.
 
Figure 2: Word allows you to find and replace non-printing characters, but Excel doesn't.
 
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 shown in Figure 2 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. As you can see in Figure 3, 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.
 
Figure 3: The SUBSTITUTE function transforms the multi-line data into a single line.
 
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.
 
As shown in Figure 4, choose Delimited, and then click Next. Then, as shown in Figure 5, 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. As you can see in Figure 6, the data is now transformed into columns!
 
Figure 4: Choose Delimited on the first screen of the Text to Columns wizard.
 
Figure 5: Choose Other, specify | as the separator, and then click Finish.
 
Figure 6: Text to columns transformed the data from rows within a single cell into columns.
 
About the author:
David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.
 

You may like these other stories...

You probably don't want to think about how many times you access the File menu in Excel 2010 or 2013. Personally I think Excel 2010 has the best possible File menu arrangement, other than having Print Preview grafted...
Following other recent high-profile hacking events, investigators discovered yesterday that hackers broke into the draft work paper files of several famous CPA firms. Revealing images of the scantily clad documents have been...
For bitcoin users, the taxman cometh. And you best know how to calculate taxes owed on what the IRS calls convertible virtual currency.In March 2014, the IRS issued Notice 2014-21, which declares virtual currency will be...

Already a member? log in here.

Upcoming CPE Webinars

Sep 24
In this jam-packed presentation Excel expert David Ringstrom, CPA will give you a crash-course in creating spreadsheet-based dashboards. A dashboard condenses large amounts of data into a compact space, yet enables the end user to easily drill down into details when warranted.
Sep 30
This webcast will include discussions of important issues in SSARS No. 19 and the current status of proposed changes by the Accounting and Review Services Committee in these statements.
Oct 23
Amber Setter will show the value of leadership assessments as tools for individual and organizational leadership development initiatives.
Oct 30
Many Excel users have a love-hate relationship with workbook links. For the uninitiated, workbook links allow you to connect one Microsoft Excel spreadsheet to other spreadsheets, Word documents, databases, and even web pages.