How to Split Text in Excel into Multiple Rows: 6 Methods

Method 1 – Splitting Text into Multiple Rows Using FILTERXML and SUBSTITUTE Functions 

Steps:

• Rearrange our data in a simpler way.

• Type the following formula in cell B7.

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

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

You can easily split your text into multiple rows.

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

Steps: 

• Select the range of cells that contain the texts that you want to split. The range is C4:C8.

• Go to Data >> Text to Columns.
• The Text to Columns wizard will appear. Split our texts by delimiter, check this in the wizard.
• Click Next.

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

• You will see a preview of how your data will look after this operation. Click Finish.

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

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

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.

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

Method 3 – Applying Power Query Editor to Split Text into Multiple Rows

Steps:

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

You will see your data in a Power Query Editor.

• 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.
• Split each text, so we select Each occurrence of the delimiter in the Split at section
• Split texts into rows, and we select Advanced options >> Rows.
• Click OK.

You will see all the texts split into multiple rows.

• If you want to load this table in an Excel sheet, click 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.

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

Steps:

• Open Visual Basic from the Developer Tab.

• You will see the VBA window appear. 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

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.

• Go back to your sheet and type the following formula in cell B7

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 ENTER, 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.

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

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

Steps:

• Follow Section 4 to open the VBA Module.
• 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

We define our Function TextSplitManual as String. This will take both Text and Delimiter as strings. We 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.

• Go back to your sheet and type the following formula in cell B7

• Hit the ENTER button to see the first text in cell B5 which is the product name Laptop.

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

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

See the first 2 texts manually. If you have a change of mind and want to see the prices, 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.

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

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

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

Easily split text into multiple rows by using the VBA Do While Loop.

Download Practice Workbook

Related Articles

• How to Split Text after a Certain Word in Excel
• Split Text by Space with Formula in Excel
• How to Split First And Last Name in Excel
• Split String by Length in Excel
• How to Separate Two Words in Excel

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