This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Parse Multi-Line Cells
Problem: Someone used the Alt+Enter trick discussed later in this book to build address information with three lines in single cells. I need to break this data into columns.
Figure 253 They used Alt+Enter to enter multi-line data in one cell.
Strategy: The Alt+Enter keystroke creates a character code 10. I’ve used many tricks to solve this, including =SUBSTITUTE(A1,CHAR(10),",") to change the line feeds to commas. But, the solution is much simpler than this.
Select the data. Use Data, Text to Columns. In Step 1, choose Delimited. In Step 2, choose Other. Click in the Other box and press Ctrl+J. Magically, the data preview will show each line of the cell going to a new column. Apparently, Ctrl+J inserts a character 10 in the Other box.