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 separate lines (visually it is a paragraph break).
Unlike the situation with unrecognised spaces though, there is no character to copy and paste into the Text to Columns.
Table of contents
YouTube Line break as the delimiter in Text to Columns
Watch, like, subscribe
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.
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).
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.
You can now continue as normal with your Text to Columns.
Related
Removing line breaks from cells (the ALT ENTER’s)
Using multiple characters as delimiters in Excel Text to Column