Split Text into Multiple Rows in Excel

Most of the time, the data in Excel is arranged in columns, and it is more common to split the text in a cell into multiple columns rather than rows.

But in some cases, you may want to split the text into multiple rows in Excel.

For example, if you have multiple names in a single cell and you want to slit these names and get these in a column in different rows.

Or you may have an address in a cell, and you want to split different parts of the address and get them in different rows.

In this article, I will show you some simple formulas and methods you can use to quickly split text in a cell into multiple rows (depending on the delimiter)

Click here to download the example file and follow along

Split Text into Rows Using TEXTSPLIT Function

If you’re using Excel for Microsoft 365 (Windows or Mac) or Excel for the Web, you can make use of the new TEXTSPLIT function.

TEXTSPLIT function splits the text in a cell into rows or columns based on the specified delimiter.

It works just like the Text-to-Columns functionality in Excel, but since this is a formula, it’s even better.

Let me show you some examples where you can use this function.

Split Based on One Delimiter

Below is an example where I have an address in cell A2, and I want to split different parts of the address (i.e., name, street name, city, and State/pin code) into different rows in a column.

In this case, these different parts of the address are separated by a comma, which I will use as the delimiter to do the split.

Below is the formula that will split the content in the cell into separate rows:

=TEXTSPLIT(A2,,", ")

The above TEXTSPLIT formula takes three arguments:

• text – This is the first argument where I’ve used the cell reference of the cell (A2) that contains the text that I want to split
• col_delimiter – This is the second argument where you are supposed to specify the delimiter based on which the split would happen in separate columns. In this case, since I want the text to be split into rows instead of columns, I have left this argument blank.
• [row_delimiter] – This is the third argument where I need to specify the delimiter based on which the text would be split into separate rows. Since I want the address to be split after each comma, I have used “, ” as the delimiter. Note that your delimiter needs to be in double quotes.

Note: The TEXTSPLIT function also has three more optional arguments, which we didn’t need in this example. You can read more about this function here.

Also, in the above example, I have used “, ” as the delimiter (i.e., a comma followed by a space character).

This is because every part of the address was followed by a comma and then a space character before the next part starts. In case your data only has a comma without the space character, you can change the delimiter accordingly.

Alternatively, you can use the below formula that can handle all types of situations:

=TRIM(TEXTSPLIT(A2,,","))

The TRIM function ensures that there are no leading space characters in any of the cells.

Also read: How to Split Cells in Excel (separate into multiple columns)

Split Based on Two or More Delimiters

TEXTSPLIT can also handle situations where you want to split the text in the cell based on two or more delimiters.

Below is this example where I have an address in cell A2, and there are two delimiters (a dash separates the name and the address, and a comma separates all the different elements of the address)

Here is one single formula that would consider two delimiters while splitting the text in the cell into rows:

=TRIM(TEXTSPLIT(A2,,{",","-"}))

In the above example, I have the following arguments in the TEXTSPLIT function:

• text – This is the cell reference of the cell (A2) that I want to split
• col_delimiter – This is the column delimiter, but since I want the text to be split into rows instead of columns, this argument is left blank.
• [row_delimiter] – This is the third argument where I need to specify the delimiter based on which the text would be split into separate rows. Since I want the address to be split based on two delimiters (dash and comma), I have used an array {“,”,”-“}. This tells the formula to check for all the delimiters specified in the curly brackets.

Split Based on Line Breaks

Another situation where you may want to split a cell’s content into rows is when there are line breaks in the cell, and you want to get each line in a separate cell in a column.

Below, I have this example where I have the address in cell A2, and there are multiple line breaks in the address.

Instead of getting multiple lines in the same cell, I want to split the content of this address so that I get each line in a separate row in a column.

This can again quickly be done using the TEXTSPLIT function, where I need to somehow use a line break as the delimiter.

And how do I do that?

By using a formula that returns a line break, which can then be used within the TEXTSPLIT function as the delimiter.

Below is the formula that will split the address using line breaks:

=TEXTSPLIT(A2,,CHAR(10))

In the above formula, I have used CHAR(10) as the row delimiter, where CHAR(10) returns the line break character.

Also read: How to Split Multiple Lines in a Cell into a Separate Columns

Split Multiple Cells into Rows in One Column

One particular case where the TEXTSPLIT function is a lifesaver is when you have data in multiple rows in a column, and you want to split each cell but still get the result in one column,

Below is an example of what I mean.

I have names in three cells in column A that are separated by commas, and I want to split all these names so that I get individual names in separate cells in the same column (as shown below).

I imagine it would be a complex formula to do this without TEXTSPLIT, but with it, it’s not that hard.

Here is the formula to do this:

=TRIM(TEXTSPLIT(TEXTJOIN(",",,A2:A4),,","))

The trick in this formula is to use the TEXTJOIn function to combine all the names in different cells so that we get one single array of names that are separated by a comma.

In the text join function, the first argument is the delimiter (which is a comma in this case) that is used when combining the content of all the cells.

The result of the TEXTJOIN function is:

“Stephen, James, Anna,Richard, Helen, Toby,Chris, Mark, Doug”

This array is then used in the TEXTSPLIT function which then splits it using comma as the delimiter.

Caution: Remember that TEXTSPLIT is a new function in Excel for Microsoft 365 and would not have backward compatibility with other functions. So, if you use it in your workbook and send it to someone who’s using an older version of Excel, they will see an error.

Click here to download the example file and follow along

Also read: Separate Text and Numbers in Excel

Split Text into Rows Using Text-to-Columns and Transpose

If you’re not using Excel for Microsoft 365, you will not have access to the TEXTSPLIT function.

So, you will have to rely on the built-in Text to Columns functionality to first split the text into columns and then transpose the result.

Let me show you how it works.

Below is an example where I have an address in cell A2 that I want to split (using a comma as the delimiter) and get the result in separate rows.

The first step would be to use Text to Columns to split it into columns. This can be done using the below steps:

1. Select the cell (or range of cells) that have the data
2. Click the Data tab

3. Click on the Text to Columns icon (it’s in the Data Tools group)

4. In the ‘Convert Text to Columns Wizard’, select the Delimited option and then click on Next.

5. Select ‘Comma’ as the delimiter (uncheck all the other options) and click on Next.

6. Select the Destination cell where you want the result, then click Finish. In this case, I would select A4 as the destination cell.

This will give you the result shown below, where it has split our data, but we have got it in columns instead of rows.

So now we will use the transpose functionality to transpose this data.

Here are the steps to do this:

1. Select the cells that you want to transpose.
2. Copy these cells. You can either use the shortcut Control + C, or right-click on the selection and then click on Copy.
3. Right on the destination cell where you want the transposed result.
4. Hover the cursor over the Paste Special option (at the arrow icon to show more options. Click on the Transpose option.

The above steps would give you the transposed data, and then you can delete the result of text to columns if you want.

While this does work, it’s not as elegant as the TEXTSPLIT function method. It involves a lot more steps and has the following limitations:

• The result of this method is static, unlike the TEXTSPLIT function method, where the result is dynamic. So if you change the original data, the result will not update (while it would when you use the TEXTSPLIT function)
• You can only split based on one delimiter, while with the TEXTSPLIT function, you can use multiple delimiters.

Also read: Separate First and Last Name in Excel

Split Text into Rows Using VBA (Custom Function)

If you don’t have the TEXTSPLIT function in your version of Excel, you can a good workaround would be to create your own function using VBA.

It’s called a User Defined Function (UDF).

It’s pretty straightforward, and once the function is created, you can use it like any other function in the worksheet.

Below is the VBA code that will create a function that will split the text in a cell into rows (based on the specified delimiter).

'Code developed by Sumit Bansal from https://trumpexcel.com

Function SplitCellToRows(CellValue As Range, Delimiter As String)
    Dim SplitValues() As String
    
    'Split the value by the specified delimiter into an array
    SplitValues = Split(CellValue.Value, Delimiter)
    
    'Go thorugh each element of the array and remove any leading or trailing spaces
    For i = LBound(SplitValues) To UBound(SplitValues)
            SplitValues(i) = Trim(SplitValues(i))
    Next i
    
    'Return the array
    SplitCellToRows = WorksheetFunction.Transpose(SplitValues)
    
End Function

The above function (called SplitCellToRows) takes two arguments:

• The cell reference of the cell that has the text that we want to split into rows
• The delimiter in double quotes

Click here to download the example file and follow along

Where to Put this Code?

Below are the steps to put this code in the VB Editor:

1. Hold the ALT key and then press the F11 key. This is going to open the VB editor. Alternatively, you can also click on the Developer tab and then click on the Visual Basic icon in the ribbon.
2. In the VB editor menu, click on the Insert option and then click on Module. This is going to insert a new module where we are going to copy-paste our above custom function code.

3. Copy-paste the above VBA code into the module code window.

4. Click on the Save icon in the toolbar (or use Control + S)

5. Close the VB Editor to go back to the worksheet.

Once you have placed the code in the VB editor, you can go back to the worksheet and use this function just like any other regular worksheet function.

How to Use this VBA Custom Function?

Below is an example where I have an address in cell A2 that I want to split into rows.

I can use the formula below to get the result in cell A4:

=SplitCellToRows(A2,",")

The above formula takes two arguments:

• A2 – This is the cell reference of the cell that has the address
• “,” – Since my delimiter is a comma, I’ve specified that within double quotes.

Things to Know When Using VBA-Created Functions

• If you want to reuse this function later, you need to save your file with a macro-enabled workbook extension (.xlsm)
• Unlike other worksheet functions, a User Defined Function (UDF) created with vb would not show you any intellisense or help regarding the arguments of the function. You need to know how many and what type of arguments the function would take.
• If you share this workbook with other people, the function will not work on their file unless they also add the function code to their VB Editor.

These are some of the methods you can use to split the text into rows in Excel.

If you’re using Excel for Microsoft 365, you don’t need to look beyond the TEXTSPLIT function, as it can handle this easily.

If you do not have access to the TEXTSPLIT function, you can either use the Text to Columns feature and then transpose the result, or you can create your own custom function using VBA.

Other Excel articles you may also like: