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. 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.
t
- They used Alt+Enter to enter multi-line data in one cell.
- Ctrl+J solves the problem.