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.
Nội dung
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:
B1 | C1 | D1 |
---|---|---|
Apple | Banana | Orange |
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:
A |
---|
Apple |
Banana |
Orange |
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
Method | Splits Into Columns | Splits Into Rows | Dynamic | Requires VBA |
---|---|---|---|---|
Text to Columns | ✅ | ❌ | ❌ | ❌ |
TEXTSPLIT Function | ✅ | ✅ | ✅ | ❌ |
FILTERXML (Older Excel) | ✅ | ✅ | ✅ | ❌ |
VBA Macro | ✅ | ✅ | ✅ | ✅ |
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