How to Split Cell Content into Multiple Rows in Excel (Scenarios + Tools Comparison)
This guide walks you through how to split the contents of a cell into multiple rows in Excel. It covers two common scenarios: one where only the target cell is split (ignoring other columns), and another where the cell is split while retaining the data in the rest of the row. Several methods are provided to handle both situations effectively, ranging from built-in Excel functions to advanced tools like Power Query, VBA, and third-party plugins.
Two Common Splitting Scenarios
Splitting data can take several forms depending on whether you need to preserve other columns in your dataset. Here we define the two primary approaches so you can choose the best one for your needs.
Scenario 1: Split Only the Target Cell (Ignore Other Columns)
Sometimes, all you care about is the values within a single cell. You want to break them into rows for easier reading, without duplicating any associated data in the same row.
Example
A,B,C
Result:
A
B
C
Use case: Light data cleaning or display tasks where other columns are irrelevant.
Scenario 2: Split Cell and Keep Other Column Data
In other cases, you’ll need each split value to be paired with the original row’s data — essentially expanding the row into multiple entries.
Example
USA | A,B,C
Result:
USA | A
USA | B
USA | C
Use case: Data normalization, where maintaining relationships between columns is essential.
When Do You Need to Split a Cell into Multiple Rows?
Step-by-Step Methods for Splitting Cells
- Method 1: Excel Native Functions (Text to Columns + Transpose paste option)
- Method 2: TRANSPOS(TEXTSPLIT)) Function–(Microsoft 365)
- Method 3: Power Query – Split and Expand Rows (Microsoft 2016 and later versions)
- Method 4: Kutools Plugin – Clicks for Splitting
- Method 5: VBA Script – Full Custom Control
Comparison of Methods for Splitting One Cell into Multiple Rows in Excel
When a cell contains multiple values separated by commas or other delimiters, Excel provides several ways to split those values into separate rows. Some methods are suitable only for splitting the target cell, while others can also preserve and repeat the related data from other columns. The following table compares the five methods introduced in this article.
| Feature | Text to Columns + Transpose | TRANSPOSE + TEXTSPLIT | Power Query | Kutools | VBA |
|---|---|---|---|---|---|
| Split one cell into multiple rows | ✔ | ✔ | ✔ | ✔ | ✔ |
| Keep related data from other columns | ✘ | ✘ | ✔ | ✔ | ✔ |
| Process multiple source rows | Limited | Requires formulas | ✔ | ✔ | ✔ |
| Supports custom delimiters | ✔ | ✔ | ✔ | ✔ | ✔ Code adjustment |
| Dynamic result when source changes | ✘ | ✔ Automatic | ✔ Refresh | ✘ Re-run required | ✘ Re-run required |
| Requires formulas | ✘ | ✔ | ✘ | ✘ | ✘ |
| Requires VBA / coding | ✘ | ✘ | ✘ | ✘ | ✔ |
| Manual copy / transpose required | ✔ | ✘ | ✘ | ✘ | ✘ |
| Suitable for large datasets | Limited | Moderate | Excellent | Excellent | Good |
| Excel version requirement | Widely supported | Microsoft 365 with TEXTSPLIT and dynamic arrays | Excel 2016 or later | Requires Kutools for Excel | Desktop Excel with VBA support |
| Setup process | Use Text to Columns to split the cell horizontally, copy the results, and use Transpose when pasting. | Enter a formula such as =TRANSPOSE(TEXTSPLIT(A1,",")). | Load the data into Power Query, choose Split Column by Delimiter, select Split into Rows, and load the results. | Use Split Cells for a single cell or Split Data to Rows when related column data must also be preserved. | Open the VBA Editor, insert the appropriate script, adjust the range or delimiter if necessary, and run the macro. |
| Best for | Simple one-time splitting of a single cell | Dynamic splitting in Microsoft 365 | Large, repeatable, and refreshable data transformations | Quick splitting of individual cells or complete datasets without formulas or code | Customized splitting workflows for users comfortable with programming |
| Ease of use | Easy but manual | Easy | Moderate | Very easy | Advanced |
When Do You Need to Split a Cell into Multiple Rows?
Before diving into the methods, it’s important to understand why you might need to split a cell. This operation is often part of data cleaning, restructuring, or visualization workflows.
Typical use cases include:
- Multiple values in a single column: such as tags, IDs, or addresses stored together.
- Pivot tables or charts: which work better with normalized data formats.
- Imported data cleanup: especially from CRM exports or survey tools.
- Data standardization: making your table database-friendly and analytics-ready.
Step-by-Step Methods for Splitting Cells
Now that you know your scenario, let’s explore different methods to perform the split — from built-in features to advanced automation. Choose the method that fits your technical comfort and frequency of use.
Method 1: Excel Native Functions (Text to Columns + Transpose paste option)
This is a straightforward, manual method for one-off cases using only Excel's built-in features.
Suited for: Scenario 1 Split Only the Target Cell (Ignore Other Columns)
Steps:
Step 1. Select the cell.
Step 2. Select Data > Text to Columns.
Step 3. In the Convert Text to Columns Wizard,
Select Delimited, click Next.
Select Comma., click Next.
Select destination cell, click Finish.
Now the cell is splited by a comma into several columns.
Step 4. Select the split cells, and press Ctrl + C keys to copy them. Right-click your destination cell, Under Paste Options, select the Transpose icon.
The contents are transposed in rows.
| Pros | Cons |
|
|
Method 2: TRANSPOS(TEXTSPLIT)) Function–(Microsoft 365)
This method combines the TEXTSPLIT function (which splits text based on a delimiter) with TRANSPOSE to instantly display the split values vertically in rows.
Suited for: Scenario 1 Split Only the Target Cell (Ignore Other Columns)
Steps:
Step 1. Enter the formula into a blank cell where you want the vertical list to start.
Formula Example: cell A1 is the target cell that used to split to rows.
In this case, the formula is
Tip: You can change the delimiter "," to ";" or "|" depending on how your data is separated.
Step 2. Press the Enter key.
| Pros | Cons |
|
|
Method 3: Power Query – Split and Expand Rows (Microsoft 2016 and later versions)
Power Query is ideal for structured, repeatable operations. It’s powerful, refreshing, and great for batch data transformations.
Suited for: Both of Scenario 1 (Split Only the Target Cell), and Scenario 2 (Split Cell and Keep Other Column Data)
Steps: Take Scenario 1 (Split Only the Target Cell) as an Example
Step 1. Select your range, go to Data > Get & Transform > From Table/Range.
an
Step 2. In the Create Table, click OK. (If your selected range has headers, tick My table has headers.)
Step 3. In the Power Query Editor, use Split Column by Delimiter.
Step 4. Select or enter the delimiter, and then click Advanced to expand the options, select Split into Rows, then click OK.
Step 5. Select Close & Load > Close & Load or Close & Load To.
Close & Load
Function: Directly loads the query results to the default location.
Close & Load To...
Function: Provides customizable loading options, allowing users to choose where and how data is stored. Best for: Regular reports, large datasets.
The same steps apply to Scenario 2 (Splitting Cells While Preserving Other Column Data) - let's examine the results.
The target range of cells:
Result:
Note: In the Power Query Editor window, select only the target column that you want to split into rows, then click By Delimiter under Split Column.
| Pros | Cons |
|
|
Tip: Add Trim and Clean steps to fix whitespace or special characters.
Method 4: Kutools Plugin – Clicks for Splitting
If you frequently split cells and prefer a GUI-based solution, Kutools is an excellent tool. It simplifies complex operations into a few clicks.
Suited for: Both of Scenario 1 (Split Only the Target Cell), and Scenario 2 (Split Cell and Keep Other Column Data)
Steps: Scenario 1 (Split Only the Target Cell)
Step 1. Select the target cell.
Step 2. Go to Kutools > Merge & Split > Split Cells.
Step 3. Choose Split into Rows and define the delimiter. Click OK./p>
Step 4. Select a destination cell. Click OK.
Steps: Scenario 2 (Split Cell and Keep Other Column Data)
Step 1. Select the column you want to split to rows.
Step 2. Go to Kutools > Merge & Split > Split Data to Rows.
Step 3. Define the delimiter. Click OK.
The same steps apply to Scenario 2 (Splitting Cells While Preserving Other Column Data) - let's examine the results.
The target range of cells:
Result:
| Pros | Cons |
|
|
Note: The 30-day Trial version offers all functionality for testing.
Method 5: VBA Script – Full Custom Control
For users with programming knowledge, VBA provides ultimate flexibility. You can tailor scripts to match your exact data layout and business rules.
Suited for: Both of Scenario 1 (Split Only the Target Cell), and Scenario 2 (Split Cell and Keep Other Column Data)
Steps:
Step 1. Press Alt + F11 to open the VBA editor.
Step 2. Click Insert > Module.
Step 3. Copy and paste the VBA below.
Scenario 1 Split Only the Target Cell
'Update by Extendoffice
Sub SplitCellToRows_Simple()
Dim InputCell As Range
Dim Values As Variant
Dim i As Long
Set InputCell = Range("A1") ' change A1 to your target cell
Values = Split(InputCell.Value, ",")
For i = 0 To UBound(Values)
InputCell.Offset(i, 1).Value = Trim(Values(i))
Next i
End Sub
Scenario 2 Split Cell and Keep Other Column Data
'Update by Extendoffice
Sub SplitRowsWithOtherColumns()
Dim ws As Worksheet
Dim LastRow As Long, i As Long
Dim arr As Variant, j As Long
Dim SplitVals As Variant
Dim CurrentRow As Long
Set ws = ActiveSheet
LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 2 Step -1
If InStr(ws.Cells(i, 2).Value, ",") > 0 Then
SplitVals = Split(ws.Cells(i, 2).Value, ",")
For j = UBound(SplitVals) To 1 Step -1
ws.Rows(i + 1).Insert Shift:=xlDown
ws.Cells(i + 1, 1).Value = ws.Cells(i, 1).Value
ws.Cells(i + 1, 2).Value = Trim(SplitVals(j))
Next j
ws.Cells(i, 2).Value = Trim(SplitVals(0))
End If
Next i
End Sub
Note: This VBA script is configured to work specifically when your data is located in columns A to B and the values to be split are in column B.
| Cons |
|
💡Tips:
- Always back up your data before running a macro.
- Replace "," in the script with your actual delimiter (e.g., ";" for semicolons or "|" for pipe characters) to match the format of your data.
Conclusion
Text to Columns + Transpose is a simple built-in solution for occasional tasks, but it involves several manual steps and does not update when the original cell changes. TRANSPOSE + TEXTSPLIT is much more efficient for Microsoft 365 users because the result is dynamic and automatically responds to changes in the source cell. However, both methods are primarily suited to cases where only the contents of the target cell need to be split.
Power Query is the strongest built-in option for larger and repeatable data-cleaning tasks. It can split values into rows while preserving related data in other columns, scales well with larger datasets, and allows the results to be refreshed when the source data changes. The trade-off is a longer setup process and a steeper learning curve.
VBA provides the greatest level of customization, but it requires programming knowledge and the workbook must be saved in a macro-enabled format if the script needs to be retained. It is therefore more appropriate for customized workflows than for users looking for a quick solution.
Kutools for Excel provides the most straightforward GUI-based approach. Split Cells can quickly split the contents of an individual cell into rows, while Split Data to Rows can expand a complete dataset and preserve the corresponding values in other columns. This makes Kutools particularly convenient when users need to handle both simple cell splitting and more complex data normalization without formulas, Power Query steps, or VBA code.
Common Issues
- Blank rows or columns: Usually caused by trailing delimiters or hidden spaces.
- Merged cell errors: Unmerge cells before starting.
- Power Query not refreshing: Remember to click Refresh after modifying source data.
- Wrong delimiter: A semicolon vs comma mistake can throw off entire results.
Relative Articles:
- Transpose Range utility: Easily convert multiple rows to columns and rows in Excel
- Unit Converter tool: Quickly convert decimal number to binary/octal/hex number or vice versa
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in