How to Use a Line Break as a Delimiter in Excel Text to Columns (2 Methods)

Method 1 – Using Text to Columns from the Data Tab

Steps:

Select cell B5.
Enter the name ‘Elijah Williams’.

Press Alt+Enter to create a line break to start a new line in the cell.
Enter the address ‘187 Clousson Road’.

Press Alt+Enter to create one more line.
Press it for every line break.

We get the following text in the cell.

Do the same for all other cells.

You will get the following dataset.

Create some columns where you would like to put the text after splitting.

Select the range of cells B5 to B8.

Go to the Data tab on the ribbon.
Select the Text to Columns option from the Data Tools group.

The Convert Text to Columns Wizard dialog box will appear.
Select Delimited.
Click Next.

Select Other in the Delimiters section.
There is a blank box beside the Other.
Press Ctrl +J. You will get a dot.
Click Next.

Change the destination.
Click Finish.

We will get the desired result. See the screenshot.

Method 2 – Embedding VBA to Use Line Break as a Delimiter

Steps

Go to the Developer tab on the ribbon.
Select the Visual Basic option from the Code group.

It will open up the Visual Basic window.
Go to the Insert tab at the top.
Select the Module option.

A Module code window will appear.
Enter the following code:

Sub SplitLines()
Selection.TextToColumns Destination:=Range("C5:F8"), DataType:=xlDelimited, Other:=True, OtherChar:=vbLf
End Sub

Close the Visual Basic window.
Select the range of cells B5 to B8.

Go to the Developer tab on the ribbon.
Select the Macros option from the Code group.

The Macro dialog box will appear.
Select SplitLines from the Macro name section.
Click Run.

We will get the following results. See the screenshot.

How to Split a Cell by Line Break Using a Formula in Excel

Steps

Create a dataset that contains text using the line break.

Create some columns where you want to put the text after splitting.

Select cell C5.
Enter the following formula using the combination of LEFT, SEARCH, and CHAR functions:

=LEFT(B5, SEARCH(CHAR(10),B5,1)-1)

Breakdown of the Formula

LEFT(B5, SEARCH(CHAR(10),B5,1)-1): Here, the SEARCH function gives the total number of characters from the string B5, and the LEFT function returns the characters from the string which is before the line break. You need to minus 1 to get the data excluding space. The CHAR function provides the line break character.

Press Enter to apply the formula.

Drag the Fill Handle icon down the column.

Select cell D5.
Enter the following formula using the combination of MID, SEARCH, and CHAR functions:

=MID(B5, SEARCH(CHAR(10),B5) + 1, SEARCH(CHAR(10),B5,SEARCH(CHAR(10),B5)+1) - SEARCH(CHAR(10),B5) - 1)

Breakdown of the Formula

MID(B5, SEARCH(CHAR(10),B5) + 1, SEARCH(CHAR(10),B5,SEARCH(CHAR(10),B5)+1) – SEARCH(CHAR(10),B5) – 1): Here, we utilize the MID function to get the middle text. To get this, first, need to define the text. We take B5 as our text.

Then, we have to define the start number. To do this, we use the combination of SEARCH and CHAR functions.

SEARCH(CHAR(10),B5) + 1: This formula provides the start number after the line break. It returns 7, which is the start number of our middle text.

SEARCH(CHAR(10),B5,SEARCH(CHAR(10),B5)+1) – SEARCH(CHAR(10),B5) – 1): This formula provides the total number of characters of the middle text. It returns 4 which denotes the total number of characters in the middle text.

Finally, the MID function uses this value and returns the middle text from the given text value.

Press Enter to apply the formula.

Drag the Fill Handle icon down the column.

Select cell E5.
Enter the following formula using the combination of RIGHT, LEN, SEARCH, and CHAR functions:

=RIGHT(B5,LEN(B5) - SEARCH(CHAR(10), B5, SEARCH(CHAR(10), B5) + 1))

Breakdown of the Formula

RIGHT(B5,LEN(B5) – SEARCH(CHAR(10), B5, SEARCH(CHAR(10), B5) + 1)): Here, the RIGHT function takes the text and the total number of characters and returns the text from the right side.

LEN(B5) – SEARCH(CHAR(10), B5, SEARCH(CHAR(10), B5) + 1: Here, the LEN function provides the total length of the text. So, it will return 12 as the length of the text on cell B5. Then, the SEARCH and CHAR function denotes the total number of characters, including the last line break. It returns 11. So, the difference between these two is 1. The RIGHT function will take the text and return one character from the right as output.

Press Enter to apply the formula.

Drag the Fill Handle icon down the column.

Things to Remember

You need to apply the line break by pressing Alt+Enter. Otherwise, the text-to-column feature will not count as a delimiter. So, just giving some space in the cell is not enough to split.
To split the text, You must use the Ctrl+J command in the Other section. Otherwise, you won’t get the desired result.

How to Use a Line Break as a Delimiter in Excel Text to Columns (2 Methods)

Method 1 – Using Text to Columns from the Data Tab

Method 2 – Embedding VBA to Use Line Break as a Delimiter

How to Split a Cell by Line Break Using a Formula in Excel

Things to Remember

Related Articles

Leave a reply Cancel reply