How to Split Text in Excel into Multiple Rows (6 Quick Tricks)

The article will provide you with some effective methods on how to split text in Excel into multiple rows. We often do this when we have several pieces of information in one cell. It becomes easy for us to see the data in separate rows.
In the dataset, we have information about some products from a shop, their product code in one column; name, brand, and price altogether in another column.

How to Split Text in Excel into Multiple Rows: 6 Suitable Ways

1. Splitting Text into Multiple Rows Using FILTERXML and SUBSTITUTE Functions

We can use a formula using the FILTERXML and SUBSTITUTE Functions to split our text. Let’s see the procedure below. (We will omit the product code column for our convenience as it doesn’t contain multiple texts)

Steps:

First, we rearrange our data in a simpler way.

Now, type the following formula in cell B7.

=FILTERXML("<s>" &SUBSTITUTE(B5,";", "</s><s>") & "</s>", "//s")

The formula here splits the texts in cell B5 into the 7th to 9th rows based on Semicolon (;).

Press the ENTER button and you will see the text in cell B5 split into different rows.

Drag the Fill Handle icon to the right to AutoFill the following cells.

Thus you can easily split your text into multiple rows.

2. Using Text to Columns Feature and TRANSPOSE Function to Split Text into Multiple Rows

Another way to split a text into multiple rows is to apply the Text to Columns Feature from the Data Tab and then use the TRANSPOSE Function. Let’s go through the process below.

Steps:

First, select the range of cells that contain the texts that you want to split. In my case the range is C4:C8.

Then go to Data >> Text to Columns.
The Text to Columns wizard will appear. We want to split our texts by delimiter, so check this in the wizard.
Click on Next.

Our preferred delimiter is a Semicolon(;), so check Semicolon and click Next.

After that, you will see a preview of how your data will look like after this operation. Just click on Finish.

Then you will see the split data in your Excel sheet.

Our goal was to split these texts into multiple rows, so we are going to use a formula that will do it. Type the following formula in cell B10.

=TRANSPOSE(C4:E8)

The TRANSPOSE function converts the columns to rows and rows to columns. By doing that, it splits our desired texts into 10th to 11th rows.

Hit ENTER and you will see all the texts split into multiple rows.

Thus you can split text into multiple rows by using the Text to Columns Feature and TRANSPOSE function.

3. Applying Power Query Editor to Split Text into Multiple Rows

We can also use the Power Query Editor from the Data Tab. Let’s follow the steps below.

Steps:

Select the cells B4:C8 and go to Data >> From Table/Range
A dialog box will appear. Just click OK. Make sure that you check My table has headers.

After that, you will see your data in a Power Query Editor.

Next, select the Product; Brand; Price column from the Power Query Editor.
Go to Home >> Split Column >> By Delimiter

The Split Column by Delimiter window will open after that. As our texts have semicolons, we chose Semicolon as our delimiter.
We want to split each text, so we select Each occurrence of the delimiter in the Split at section
As we want to split texts into rows, we select Advanced options >> Rows.
Then we click OK.

After that, you will see all the texts split into multiple rows.

If you want to load this table in an Excel sheet, just click on Close & Load.

This operation will transfer these data into a new Excel sheet.

Thus you can split a text into multiple rows with the help of the Power Query Editor.

4. Creating a Function Using VBA to Split Text into Multiple Rows in Excel

The most effective way to split text into multiple rows will be to create a user-defined function using VBA and then put an Excel formula according to our convenience. Let’s go through the process below.

Steps:

First, open Visual Basic from the Developer Tab.

You will see the VBA window appear. Now, go to Insert >> Module.

Now type the following code in the VBA Module.

Function TextSplit(Text As String, Delimiter As String) As String()
On Error Resume Next
TextSplit = Split(Text, Delimiter)
On Error GoTo 0
End Function

Here, we define our Function TextSplit as String. This will take both Text and Delimiter as strings. The VBA Split Function will split the texts based on Delimiters.

Now go back to your sheet and type the following formula in cell B7

=TRANSPOSE(TextSplit(B5,";"))

The TextSplit function will split the texts into columns first and then it will split into rows with the help of the TRANSPOSE function.

Hit the ENTER button and you will see the text in cell B5 splits into 3 different rows.

Drag the Fill Handle icon to the right to AutoFill the following cells.

Thus you can easily split your text into multiple rows using a user-defined function by VBA and TRANSPOSE Function.

Read More: Split String by Character in Excel

5. Using ROW & VBA Custom Functions to Split Text into Multiple Rows

If you want to split text into multiple rows manually, you can create a user-defined VBA function for this and then put an Excel formula according to our convenience. Suppose you want to see only the product name and brand at first and not the prices. Let’s help you on that purpose.

Steps:

Follow Section 4 to open the VBA Module.
Now type the following code in the VBA Module.

Function TextSplitManual(Text As String, Delimiter As String, _
Product As Integer) As String
On Error Resume Next
TextSplitManual = Split(Text, Delimiter)(Product - 1)
On Error GoTo 0
End Function

Here, we define our Function TextSplitManual as String. This will take both Text and Delimiter as strings. We also added a new variable Product as Integer which will return the splitted parts of the text one by one. The VBA Split Function will split the texts based on Delimiters.

Now go back to your sheet and type the following formula in cell B7

=TextSplitManual(B$5,";",ROWS($B$5:B5))

Hit the ENTER button and you will see the first text in cell B5 which is the product name Laptop.

Drag the Fill Handle icon downward to AutoFill following cell. This will show you the 2nd text of cell B5.

After that, drag the Fill Handle icon again to the right to AutoFill the following cells.

Thus you can see the first 2 texts manually. If you have a change of mind and want to see the prices too, just drag the Fill Handle icon downward.

Thus you can easily split your text into multiple rows manually using a user-defined function by VBA and ROWS Function.

6. Using a VBA Code to Split Text into Multiple Rows Instantly

This is probably the best method of all that we used in this article. We can split text into multiple rows instantly by running this code in VBA. Let’s go through the procedure below.

Steps:

Follow Section 4 to open the VBA Module.
Type the following code in the VBA Module.

Option Explicit
Sub SplitToRows()
  Dim k As Long
  Dim Product_Info() As String
  Dim Set_Row As Long
  With Worksheets("vba")
    Set_Row = 5
    Do While True
      If .Cells(Set_Row, "C").Value = "" Then
        Exit Do
      End If
       Product_Info = Split(.Cells(Set_Row, "C").Value, ";")
      If UBound(Product_Info) > 0 Then
        .Cells(Set_Row, "C").Value = Product_Info(0)
        For k = 1 To UBound(Product_Info)
          Set_Row = Set_Row + 1
          .Rows(Set_Row).EntireRow.Insert
          .Cells(Set_Row, "C").Value = Product_Info(k)
          .Cells(Set_Row, "D").Value = .Cells(Set_Row - 1, "D").Value
        Next
      End If
      Set_Row = Set_Row + 1
    Loop
  End With
 End Sub

Code Explanation

Here, we named our Sub Procedure as SplitInToRows.
Then, we declared our variable types.
As the information of the Products begins from the 5th row, we set Set_Row to 5.
After that, a VBA If Statement is provided within the Do While loop to check whether the cell is empty or not.
Then, used the VBA Split to split the texts and kept it in the Product_Info
Next, the IF condition is as follows: when Product_Info is greater than 0 it will place the splitted values in the consecutive rows. Also used a For Loop to go through the selection.

Now go back to your sheet and Run the Macro named SplitInToRows as it is your current Macro.

After executing the command, the information of the products will be split into the lower cells.

Thus you can easily split text into multiple rows by using the VBA Do While Loop.

Practice Section

Here I have given you the dataset that we used in this article so that you can practice these methods on your own.

Download Practice Workbook

Split Text into Multiple Rows.xlsm

Conclusion

After going through this article, I hope you will get proper knowledge on how to split text in Excel into multiple rows. The ideas described here are pretty simple and easy to understand. And also, these methods are very much effective. If you have any better ideas or methods or feedback, please share them in the comment box. This will help me enrich my upcoming articles.

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

Get FREE Advanced Excel Exercises with Solutions!

How to Split Text in Excel into Multiple Rows (6 Quick Tricks)

How to Split Text in Excel into Multiple Rows: 6 Suitable Ways

1. Splitting Text into Multiple Rows Using FILTERXML and SUBSTITUTE Functions

2. Using Text to Columns Feature and TRANSPOSE Function to Split Text into Multiple Rows

3. Applying Power Query Editor to Split Text into Multiple Rows

4. Creating a Function Using VBA to Split Text into Multiple Rows in Excel

5. Using ROW & VBA Custom Functions to Split Text into Multiple Rows

6. Using a VBA Code to Split Text into Multiple Rows Instantly

Practice Section

Conclusion

Related Articles

Leave a reply Cancel reply