How to Use Line Break as Delimiter in Excel Text to Columns

Sometimes, you need to use a line break in a particular cell. This line break can be used as the delimiter in the Excel text-to-column feature. That feature will split the whole line into several columns. This article will focus on effectively using a line break as a delimiter in the Excel text-to-columns feature.

How to Use Line Break as Delimiter in Excel Text to Columns Feature: 2 Examples

In order to use line break as a delimiter in the Excel text-to-columns feature, we have found two suitable examples through which you can easily do the job. Our first example is based on using the text to columns from the data tab and the second one is based on using the VBA code. Both of these methods are extremely easy to use.

1. Using Text to Columns from Data Tab

Our first method is based on using the text-to-column from the data tab in Excel. In this method, we need to place a text in a cell using a line break. After that, we would like to use the text-to-column from the data tab to split the text into several columns effectively.

Here, we would like to take a dataset where we use some addresses, and then, we will split it into several parts using text to column feature. Let’s follow the instructions below to convert Text to Columns in Excel with line break as a delimiter!

Steps

First, select cell B5.
Then, write down the name ‘Elijah Williams’.

After that, press Alt+Enter to create a line break to start a new line in the cell.
Then, write down the address ‘187 Clousson Road’.

After that, you need to press Alt+Enter again to create one more line.
So, you need to press it for every line break. Finally, we get the following text in the cell.

Do the same procedure for all other cells.
As a result, you will get the following dataset.

Then, create some columns where we would like to put the text after splitting.

Select the range of cells B5 to B8.

Then, go to the Data tab on the ribbon.
Select the Text to Columns option from the Data Tools group.

As a result, the Convert Text to Columns Wizard dialog box will appear.
From there, select Delimited.
After that, click on Next.

Then, select Other in the Delimiters section.
There is a blank box beside the Other. Press Ctrl +J there.
As a result, you will get a dot in there.
After that, click on Next.

In the final step, change the destination.
Finally, click on Finish.

As a consequence, we will get the desired result. See the screenshot.

2. Embedding VBA to Use Line Break as Delimiter

Our next example is based on using the VBA code to use line break as a delimiter. Before doing anything, you need to enable the Developer tab on the ribbon. To use this VBA code, follow the steps carefully.

Steps

First, go to the Developer tab on the ribbon.
Then, select the Visual Basic option from the Code group.

It will open up the Visual Basic window.
Then, go to the Insert tab at the top.
After that, select the Module option.

As a result, a Module code window will appear.
Write down the following code.

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

Then, close the Visual Basic window.
Select the range of cells B5 to B8.

Then, go to the Developer tab on the ribbon.
Select the Macros option from the Code group.

Then, the Macro dialog box will appear.
Select SplitLines from the Macro name section.
After that, click on Run.

As a consequence, we will get the following results. See the screenshot.

How to Split Cell by Line Break Using Formula in Excel

We can split cell by line break using the formula in Excel. Here, you need to create a dataset that contains text using a line break. Then, using the formulas, we would like to split it into several columns. To understand the process, follow the steps properly.

Steps

First, create a dataset that contains text using the line break.

Then, create some columns where you want to put the text after splitting.

After that, select cell C5.
Write down 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.

Then, press Enter to apply the formula.

After that, drag the Fill Handle icon down the column.

Then, select cell D5.
Write down 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. So, 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.

Then, press Enter to apply the formula.

After that, drag the Fill Handle icon down the column.

Then, select cell E5.
Write down 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 character 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.

Then, press Enter to apply the formula.

After that, 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.
You have to use the Ctrl+J command in the Other section to split the text. Otherwise, you won’t get the desired result.

Download Practice Workbook

Download the practice workbook below.

Text to Columns Line Break.xlsm

Conclusion

We hope that you were able to apply the methods that we showed in this tutorial on how to use a line break as a delimiter in the Excel text-to-columns feature. As you can see, there are quite a few steps to achieve this. So carefully follow them to achieve the same result as we have produced here.

If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. If you have any queries, please feel free to contact us in the comment box.

<< Go Back to Excel Text to Columns | Splitting Text | Split in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

How to Use Line Break as Delimiter in Excel Text to Columns

How to Use Line Break as Delimiter in Excel Text to Columns Feature: 2 Examples

1. Using Text to Columns from Data Tab

2. Embedding VBA to Use Line Break as Delimiter

How to Split Cell by Line Break Using Formula in Excel

Things to Remember

Conclusion

Related Articles

Leave a reply Cancel reply