Use a Line Break in Text to Columns in Excel & Google Sheets

Written by

Editorial Team

Reviewed by

Laura Tsitlidze

Last updated on October 30, 2023

This tutorial demonstrates how to use line breaks and Text to Columns in Excel and Google Sheets.

text to column line break final data

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

text to column line break initial data

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.

text to column line break 1

In the Text to Columns Wizard, leave the default data type (delimited), and click Next.

text to column line break 2

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.

text to column line break 3

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

text to column line break 4

To start filling the split data from the original cell (B1), replacing the initial data, click OK.

text to column line break 5

Finally, the text from cell B1 is split into cells B1:B5, based on line breaks.

text to column line break final data

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:

=SPLIT(B1,CHAR(10))

google sheets split text to columns by line break

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.