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).
Now, I want to convert those names in cell A1 to multiple rows.
Below is the TEXTSPLIT formula that will do this:
=TEXTSPLIT(A1,,", ")
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).
Now, I want to convert those names in cell A1 to multiple rows. To do that, I can use the below formula.
=TEXTSPLIT(A1,,{", ",". "})
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).
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,", "))
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).
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,", "))
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).
Now, I want to convert those names in cell A1 to multiple rows. To do that, I have to follow the below steps.
- Select the cells that you want to convert text to rows and go to the Data tab.
- Go to the “Get & Transform Data” group and click the “From Table/Range” icon.
Then, Excel will pop up the “Create Table” dialog box.
- 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.
Now, Excel opens the Power Query Editor Window.
- Go to the “Home” tab of the Power Query Editor.
- Go to the “Transform” group and click the “Split Column” icon.
- Select “By Delimiter” from the expanded list.
You’ll see the “Split Column by Delimiter” dialog box.
- Select the delimiter and select the split at the “Each occurrence of the delimiter” option.
- Expand the “Advanced Options” and select the Split into “Rows” option.
- Click the “OK” button.
- Finally, Click the “Close & Load” button.
Then, you’ll get the results as an Excel Table in a 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).
Now, I want to convert those names in cell A1 to multiple rows.
To do that, I have to follow the below steps.
- Press “ALT + F11” to open the VBA Editor. If the function keys are locked, you have to press the “Fn” key also.
- Click the “Insert” and select “Module”.
- 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:
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.