Split Text to Rows in Excel

Excel has many built-in tools and functions to split text into columns (including a dedicated Text to Columns feature).

But what if you want to split text into rows instead of columns? While it may seem challenging at first, there are multiple easy ways you can use to split text into rows in Excel.

In this article, I will show you some simple methods that you can use to convert text to rows.

Method 1 – Using the TEXTSPLIT Function

Microsoft Excel recently introduced a text function called TEXTSPLIT that allows you to convert text to rows in Excel dynamically.

This function is currently available on Excel 365 and Excel on the web.

Below, I have some names separated by commas in cell A1 (as shown below).

 Data set to split text to rows

Now, I want to convert those names in cell A1 to multiple rows.

Below is the TEXTSPLIT formula that will do this:

=TEXTSPLIT(A1,,", ")
Textsplit function to split text to rows

As soon as you enter this formula in cell A3 and hit the enter key, it will give you the result as shown above (where the content of cell A1 are split into multiple cells in separate rows).

The syntax of the TEXTSPLIT function is

TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty], [match_mode], [pad_with]). 
  • text – This is the text you have in the cell that you want to convert from text to rows
  • Col_delimiter – This is the delimiter (Separator) if you are splitting the selected text into columns. As I don’t want to split the selected text into columns, I can ignore the second argument. To ignore this argument, just enter a comma.
  • Row_delimiter – This is the delimiter (Separator) if you are splitting the selected text into rows. I enter a comma and a space “, ” within quotes as the third argument.

In this scenario, I ignore all other remaining optional arguments.

Dealing with more than one delimiter

If there is more than one delimiter, you can use an array constant.

Below, I have some names separated by commas and periods in cell A1 (as shown below).

data set with comma and period as delimiter

Now, I want to convert those names in cell A1 to multiple rows. To do that, I can use the below formula.

=TEXTSPLIT(A1,,{", ",". "})
Textsplit function handling two delimiters

In this case, for the third argument, I used two delimiters inside curly brackets:

  • A comma with a space and
  • A period with a space

This way, the function checks for both delimiters in the cell and splits the content when it encounters any of the specified delimiters.

Also read: How to Split One Column into Multiple Columns in Excel

Method 2 – Using TEXTSPLIT and TOCOL Functions

While you can achieve the result with TEXTSPLIT function alone, let me show you another way to do this using a combination of TEXTSPLIT and TOCOL function.

Below, I have some names separated by commas in cell A1 (as shown below).

 Data set to split text to rows

Now, I want to convert those names in cell A1 to multiple rows. To do this, I can use the below formula.

=TOCOL(TEXTSPLIT(A1,", "))
TOCOL and TEXTSPLIT function to split text to rows in Excel

In the above formula, I have used the TEXTSPLIT function to create an array that has each name as one element of the array.

TOCOL function is then used on the result of the TEXTSPLIT function to give this array in a column.

Also read: How to Separate Names in Excel

Method 3 – Use the TEXTSPLIT and TRANSPOSE Functions

Another easy way to split text in a cell into separate rows is to combine the TEXTSPLIT function with the TRANSPOSE function.

Below, I have some names separated by commas in cell A1 (as shown below).

 Data set to split text to rows

Now, I want to convert those names in cell A1 to multiple rows. To do that, I can use the below formula.

=TRANSPOSE(TEXTSPLIT(A1,", "))
TRANSPOSE and TEXTSPLIT function to split text to rows in Excel

In the above formula, first, you split the names into columns using the TEXTSPLIT function.

The syntax of the TEXTSPLIT function is TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty], [match_mode], [pad_with]).

  • text – This is the text you have in the cell that you want to convert from text to rows
  • Col_delimiter – This is the delimiter (Separator) if you are splitting the selected text into columns. For the second argument, you can enter a comma and a space within quotes. Then, Excel split each name separated by a comma and a space into different columns.

Next, you can use the Excel TRANSPOSE function to convert the split text into rows.

The syntax of the TRANSPOSE function is TRANSPOSE(array). This function is very simple and you have to select only the array that you want to transpose. In this case, it is the result of the TEXTSPLIT function.

Also read: Separate Comma Separated Values in Excel

Method 4 – Use Power Query

If you are not an Excel 365 user or an Excel on web user, you can’t use any of the above three methods to convert text to rows.

The reason is the TEXTSPLIT function is currently available on Excel 365 and Excel on the web. Then, you can use the Excel Power Query to split text into rows in Excel.

Below, I have some names separated by commas in cell A1 (as shown below).

 Data set to split text to rows

Now, I want to convert those names in cell A1 to multiple rows. To do that, I have to follow the below steps.

  1. Select the cells that you want to convert text to rows and go to the Data tab.
Click the data tab in the ribbon
  1. Go to the “Get & Transform Data” group and click the “From Table/Range” icon.
Click on the from table range icon

Then, Excel will pop up the “Create Table” dialog box.

  1. If the selected table has headers, check the box and click the “OK” button. Otherwise, directly click the “OK” button. In this case, I don’t have headers for the selected data. So, I click the “OK” button without checking the box.
Click OK in the create table dialog box

Now, Excel opens the Power Query Editor Window.

  1. Go to the “Home” tab of the Power Query Editor.
Click the home tab in the power query editor
  1. Go to the “Transform” group and click the “Split Column” icon.
Click on the split column option in the ribbon
  1. Select “By Delimiter” from the expanded list.
Select the by delimiter option from the drop down

You’ll see the “Split Column by Delimiter” dialog box.

  1. Select the delimiter and select the split at the “Each occurrence of the delimiter” option.
Select the delimiter and each occurrence of the delimiter option
  1. Expand the “Advanced Options” and select the Split into “Rows” option.
Select the split into rows option
  1. Click the “OK” button.
  2. Finally, Click the “Close & Load” button.
Click on the closed and load option

Then, you’ll get the results as an Excel Table in a new sheet.

Power query inserts new table in new sheet

The Power Query method is the best method when you have to import data from different sources.

You can link the source data, and setting up the Query is only a one-time process. After you set the query, you have to refresh your data, and you’ll get the updated results. 

However, if you want to do the split text to rows only once, this method is not the best method for you as it involves lots of steps compared to other methods. In such a case, it’s best to use any of the formula methods covered above.

Also read: How to Separate Address in Excel?

Method 5 – Using VBA Code to Split Text into Rows

Let’s say you often have a need to convert text to rows in Excel. In such situations, using a VBA code can be a more efficient method (especially if you’re using older versions of Excel and do not have access to newer functions covered earlier)

Below, I have some names separated by commas in cell A1 (as shown below).

 Data set to split text to rows

Now, I want to convert those names in cell A1 to multiple rows.

To do that, I have to follow the below steps.

  1. Press “ALT + F11” to open the VBA Editor. If the function keys are locked, you have to press the “Fn” key also.
VB editor
  1. Click the “Insert” and select “Module”.
Insert a new module
  1. Enter the below VBA code on the Module.
'Code developed by Steve Scott from https://spreadsheetplanet.com
Sub SplitMyValuesDown()
    Dim MyValues() As String
    Dim targetRange As Range
    Dim overwriteWarning As VbMsgBoxResult
    Dim i As Long
    Dim selectedRange As Range
    
    ' Check if only one cell is selected
    If Selection.Cells.Count > 1 Then
        MsgBox "Please select only one cell."
        Exit Sub
    End If
    
    ' Check if the selected cell is empty
    If IsEmpty(Selection.Value) Then
        MsgBox "The selected cell is empty."
        Exit Sub
    End If
    
    ' Check if the value is a string
    If Not IsString(Selection.Value) Then
        MsgBox "The selected cell does not contain text."
        Exit Sub
    End If
    
    ' Split the value by ", "
    MyValues = Split(Selection.Value, ", ")
    
    ' Ask the user to select a cell for the output
    On Error Resume Next
    Set selectedRange = Application.InputBox("Select a cell where you'd like to display the result:", Type:=8)
    On Error GoTo 0
    
    ' Check if the user canceled the operation
    If selectedRange Is Nothing Then
        MsgBox "Operation canceled by the user."
        Exit Sub
    End If
    
    ' Set the target range for pasting the values
    Set targetRange = selectedRange.Resize(UBound(MyValues) + 1)
    
    ' Check if the target range has existing data
    For i = 1 To targetRange.Cells.Count
        If Not IsEmpty(targetRange.Cells(i).Value) Then
            overwriteWarning = MsgBox("This operation will overwrite existing data. Continue?", vbYesNo + vbExclamation)
            If overwriteWarning = vbNo Then
                Exit Sub
            Else
                Exit For
            End If
        End If
    Next i
    
    ' Populate the cells
    targetRange.Value = Application.Transpose(MyValues)
    
End Sub

' Helper function to check if a value is a string
Function IsString(val) As Boolean
    IsString = (VarType(val) = vbString)
End Function

To use this code, first select the cell that has the text that you want to split, and then run this code.

When you run the above VBA code, it will show you a dialog box asking you to select a cell where you want the result.

Once you select the destination cell, it will give you the result.

One big benefit of using VBA code is that it saves you time and effort because you can reuse the same code the next time you need to do this. Once you write a VBA script you can save it right within the Excel file. The next time you face the same task, you don’t have to start from scratch; just run the saved script. This makes your work faster and more consistent.

So, now you have learned simple ways that you can use to split text into rows in Excel.

Based on your Excel versions and also based on your preferences, select one of the above methods to split text into rows.

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

1 thought on “Split Text to Rows in Excel”

  1. How can I keep other data in the same row with the new rows created when breaking apart one cell? For example, an order number is listed on each line with another cell containing order details that need to be separated. After I break the detail apart, I still need to identify the details with that order number.

    Reply

Leave a Comment