Parse Multi-Line Cells

December 24, 2021 - by Bill Jelen

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.

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. 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.

Someone went overboard with Alt+Enter. Each cell in column A contains Name, Street Address, City State Zip on three lines because someone typed Alt+Enter twice in each cell. You want to break this out to three columns. — Figure 239. They used Alt+Enter to enter multi-line data in one cell.

Use Text to Columns, Delimited. In step 2, choose Other as the delimiter and type Ctrl+J in the Other box. This is inexplicably the keystroke to insert Alt+Enter in the box. The data preview shows that it worked. — Figure 240. Ctrl+J solves the problem.

Additional Details: Power Query can also solve this problem by using Split Column, To Rows.

This article is an excerpt from Power Excel With MrExcel

Title photo by Rodion Kutsaev on Unsplash