How to Split Text in Excel into Rows or Columns

When working with Excel, you may encounter cases where a single cell contains multiple values separated by commas (,), semicolons (;), or other delimiters. To split them into columns or rows, you can use various methods such as Text to Columns, Excel formulas, or VBA.

This article will guide you through the most effective ways to split text in Excel, with practical examples.

1. Using Text to Columns (Splitting into Columns)

When to Use This Method?

• When your data is separated by a specific character (e.g., comma, hyphen).
• When you want to split the data into multiple columns, not rows.
• This is a quick and easy method that does not require formulas or VBA.

How to Do It:

Suppose you have the following data in a single cell:

Mango, grapefruit, orange, tangerine, jackfruit, coconut, rambutan
Pineapple, mangosteen, guava, longan, apple, pear, watermelon

Step 1: Select the data range you want to split, then go to Data > Text to Columns.

Step 2: In the dialog box, select Delimited, then click Next.

Step 3: Choose Comma (or the appropriate delimiter), then click Next.

Step 4: Select the destination cell to place the split data, then click Finish.

Result: The data is split into separate columns.

2. Using Formulas to Split Text into Rows or Columns

When to Use This Method?

• When you need a dynamic solution (if the source data changes, the split data updates automatically).
• When you want to split text into rows instead of columns.

Splitting Text into Columns Using FORMULATEXT

Excel provides functions like TEXTSPLIT (available in Excel 365 and Excel 2019) to easily separate text into columns.

Example:

If cell A1 contains:

Apple, Banana, Orange

Use this formula in B1:

=TEXTSPLIT(A1, ", ")

Result:

Splitting Text into Rows Using TRANSPOSE and FILTERXML

If you need to split text into rows instead of columns, you can use:

=TRANSPOSE(TEXTSPLIT(A1, ", "))

Result:

For older Excel versions, you can use FILTERXML with helper formulas.

3. Using VBA to Split Text into Rows

When to Use VBA?

• When you have a large dataset and need an automated solution.
• When the delimiter is complex (e.g., multiple characters).
• When you want to split text into rows instead of columns.

Steps to Use VBA

Step 1: Press ALT + F11 to open the Visual Basic for Applications (VBA) editor.

Step 2: Click Insert > Module, then paste the following code:

Sub SplitAll()
    Dim xRg As Range
    Dim xRg1 As Range
    Dim xCell As Range
    Dim I As Long
    Dim xAddress As String
    Dim xUpdate As Boolean
    Dim xRet As Variant

    On Error Resume Next
    xAddress = Application.ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("Please select a range", "Kutools for Excel", xAddress, , , , , 8)
    Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)

    If xRg Is Nothing Then Exit Sub
    If xRg.Columns.Count > 1 Then
        MsgBox "You can't select multiple columns", , "Excel VBA"
        Exit Sub
    End If

    Set xRg1 = Application.InputBox("Split to (single cell):", "Kutools for Excel", , , , , , 8)
    Set xRg1 = xRg1.Range("A1")

    If xRg1 Is Nothing Then Exit Sub
    xUpdate = Application.ScreenUpdating
    Application.ScreenUpdating = False

    For Each xCell In xRg
        xRet = Split(xCell.Value, ",")
        xRg1.Worksheet.Range(xRg1.Offset(I, 0), xRg1.Offset(I + UBound(xRet, 1), 0)) = Application.WorksheetFunction.Transpose(xRet)
        I = I + UBound(xRet, 1) + 1
    Next

    Application.ScreenUpdating = xUpdate
End Sub

Step 3: Press F5 to run the script.

Step 4: Select the cell range containing the text you want to split, then click OK.

Step 5: Choose a destination cell for the split data, then click OK.

Result: The text values separated by commas in the selected cells are now split into rows.

4. Comparison of Methods

5. Conclusion

Splitting text into columns or rows in Excel can be done in multiple ways, depending on your needs:

• Text to Columns is great for one-time splits into columns.
• TEXTSPLIT (Excel 365/2019) is the easiest formula-based method.
• FILTERXML can be used in older Excel versions for a similar effect.
• VBA is the best solution for large datasets and automated tasks.

Choose the method that best fits your workflow and start working more efficiently with your Excel data!

See more: Guide to Enabling and Disabling Gridlines in Excel