Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.


Download Practice Workbook

Download the practice workbook below.


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

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. To understand the process properly, you need to follow the steps.

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.

Using Text to Columns from Data Tab to Use Line Break as Delimiter in Excel Text to Columns

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

Applying Text to Columns from Data Tab to Use Line Break as Delimiter in Excel Text to Columns

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

Utilizing Text to Columns from Data Tab to Use Line Break as Delimiter in Excel Text to Columns

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

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

Exercising Text to Columns from Data Tab to Use Line Break as Delimiter in Excel Text to Columns

Read More: [Fixed!] Excel Text to Columns Is Deleting Data


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 do this, follow the link How to Show 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.

Embedding VBA to Use Line Break as Delimiter in Excel Text to Columns Feature

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

Applying VBA to Use Line Break as Delimiter in Excel Text to Columns Feature

Read More: How to Convert Text to Columns with Multiple Delimiters in Excel


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.

Split Cell by Line Break Using Formula in Excel

  • 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 character 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.

Split Cell by Line Break Utilizing Formula in Excel

  • 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 returns one character from the right as output.

  • Then, press Enter to apply the formula.

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

Split Cell by Line Break Applying Formula in Excel

Read More: How to Split Text to Columns Automatically with Formula in Excel


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.

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. Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please feel free to connect us in the comment box.


Related Articles

Durjoy Paul

Durjoy Paul

Hi there! I'm Durjoy. I have completed my graduation from the Bangladesh University of Engineering and Technology. I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo