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
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.