Line break as the delimiter in Text to Columns in Excel

You may come across situations where you need to use a line break as the delimiter in Text to Columns in Excel. A line break is where, within a single cell, text appears on two seperate lines.

Unlike the situation with unrecognised spaces though, there is no character to copy and paste into the Text to Columns.

Identifying a Line Break in Excel cells

A line break is obvious when you click in a cell. As shown below, when you click in the cell, the cell looks like it is on 1 line but when you look in the formula bar it is actually on 2 lines (which implies that there is a line break character at the end of the first line).

Occasionally you will only see the one line in the cell, but when you click on it and expand the formula bar you will see other information.

Line break as the delimiter in Text to Columns

Split the column using this line break

The only trick is that you need to use a shortcut to ‘create’ the line break character using CTRL + J.

As shown below you go through the normal Text to Column steps (click on DATA ribbon, Text to Column button, then choose Delimited and Next).

Line break as the delimiter in Text to Columns

The trick is in the next screen. Choose the Other option, and while clicked in the input box, hold CTRL down and push your J button. Immediately the data preview window will show that it recognises the line breaks and will split the column based on the line breaks it sees.

Line break as the delimiter in Text to Columns

You can now continue as normal with your Text to Columns.

Convert Text to Columns

Removing line breaks from cells (the ALT ENTER’s)

Using multiple characters as delimiters in Excel Text to Column

Data Scrubbing MS Excel Files