How to extract multi-line Excel cell text into columns

In another tutorial from our sister site AccountingWEB.com, US Excel trainer David Ringstrom shows how to make multiple lines of data in one cell appear across the adjacent columns.

Data will often be entered into in Excel as multiple lines in a single cell, as shown below. It doesn’t take much effort to type in a few records, but if you have dozens or hundreds of records, converting the data into a more useable format can take some work. This article illustrates some simple techniques for transforming this data into columns.
 
There are a few different options for dealing with the line breaks. You can use the SUBSTITUTE function to replace the non-printing new line character with another character.

Or you can use Excel's Find and Replace feature to eliminate line feeds. Press Ctrl-H to display the Replace dialog box, and then hold down the ALT key while you type 0010 (that's two zeros, a one, and then a zero). It will appear that the Find field is still blank. Move to the Replace field, and enter a character, such |, which is often referred to as the Pipe symbol. Click Replace All, and you'll have swapped out the line feeds without using a formula.

You can carry out similar replacements in Word, as shown in Figure 2, but SUBSTITUTE is relatively straightforward to use within Excel.

Continued...