Use a Line Break in Text to Columns in Excel & Google Sheets
This tutorial demonstrates how to use line breaks and Text to Columns in Excel and Google Sheets.
Line Break in Text to Columns
If you have text divided by line breaks in a cell, you can use those line breaks to split the text into columns. Say you have the products list pictured below in cell B1. Each product is separated by a line break (ALT + ENTER on the keyboard).
To split text from cell B1 to Columns B–F based on line breaks, use Excel’s Text to Columns functionality.
- Select a text cell (here, B1) and in the Ribbon, go to Data > Text to Columns.
- In the Text to Columns Wizard, leave the default data type (delimited), and click Next.
- In Step 2 of the Wizard, (1) select Other. (2) Click in the text box then press CTRL + J on the keyboard. This shortcut is for a line break delimiter, and if you look closely at the box, you can see a very small blinking dot. In the data preview part below that, you can see how the text will be split. (3) Click Next.
- In Step 3 of the Wizard, leave the default options and click Finish. Here, you could also change the default data format (general) to text or date.
The Destination cell is the one with the initial data (B1).
- To start filling the split data from the original cell (B1), replacing the initial data, click OK.
Finally, the text from cell B1 is split into cells B1:B5, based on line breaks.
You can also use VBA code for text to columns to split text based on line breaks.
Line Break in Text to Columns in Google Sheets
In Google Sheets, there is no option in Text to Columns to use a line-break delimiter. Therefore, you need to use a combination of the SPLIT and CHAR Functions to achieve the same output.
In cell C1, enter the formula:
The SPLIT Function divides a given cell (C1) into adjacent columns based on a user-provided separator. Here, the separator is the result of the CHAR Function, which returns a character for a given number. The line break character is Unicode number 10.