Yes. If the text is in A2, enter =TRIM(TEXTSPLIT(A2,,",",TRUE)) in an empty cell to split comma-separated items into a vertical list. This Microsoft 365 formula spills the results into the rows below; for a larger dataset, Power Query can split a column into rows and repeat the other fields alongside each item.
What “split one cell into multiple rows” means
A cell can contain several values separated by a character, such as a comma, semicolon, or line break. Splitting it into rows means turning each separated value into its own cell in a vertical list. For example, the cell apples, pears, grapes becomes three cells: apples, pears, and grapes.
This is different from splitting a cell into multiple columns. A horizontal split places the items side by side; a vertical split stacks them. The distinction matters when preparing data for a database, pivot table, chart, or other analysis. Those tools generally work better when each item has its own row rather than several values packed into one cell.
The original cell is not automatically erased by a formula. The results appear in a separate spill range, so you can check them before deciding whether to replace the original data. Power Query also leaves the original worksheet data unchanged unless you choose to overwrite or remove it yourself.
Use TEXTSPLIT for a quick vertical list
For a single cell or a small number of cells, TEXTSPLIT is usually the simplest option. In Microsoft 365 or Excel 2024, its row-delimiter argument tells Excel to put each piece on a new row. In the opening formula, the first delimiter argument is left empty, so Excel does not split across columns; the comma is supplied as the row delimiter. TRIM removes ordinary extra spaces at the beginning or end of each item.
With the text apples, pears, grapes in A2, the formula =TRIM(TEXTSPLIT(A2,,",",TRUE)) returns apples, pears, and grapes in three vertical cells. The final TRUE tells TEXTSPLIT to ignore empty pieces, which can prevent a doubled comma from creating a blank result. Enter the formula in a clear area with enough empty cells below it: Excel needs those cells to display the spilled list.
To split at semicolons instead, with the source text still in A2, use =TRIM(TEXTSPLIT(A2,,";",TRUE)). For example, North; South; West becomes three rows. Change the delimiter to match the character that actually separates the values. Do not include a space in the delimiter if spacing varies; splitting on the comma and trimming each result is more forgiving.
For values separated by line breaks, with the text in A2 and line breaks entered within that cell, use =TEXTSPLIT(A2,,CHAR(10),TRUE). CHAR(10) represents the line-feed character commonly used for in-cell line breaks in Excel. If the result includes unexpected empty cells, inspect the source for consecutive line breaks or other invisible characters.
TEXTSPLIT is available in Microsoft 365 and Excel 2024, but not in every older perpetual version of Excel. If Excel displays a #NAME? error for the function, use Power Query or the manual method below instead. Dynamic-array formulas such as this one also cannot spill inside an Excel Table. Put the formula in a normal worksheet range outside the table, and make sure the intended spill area is clear.
Choose a delimiter that matches the data
The delimiter is the boundary between items. Common choices include commas in lists, semicolons in exported data, pipe characters in system extracts, and line breaks in manually entered lists. A split only works as intended when the chosen delimiter really marks a boundary between values.
If the data uses more than one delimiter, standardize it first or use Power Query to make the transformation easier to inspect. For a simple case where A2 contains a mixture of commas and semicolons, this formula replaces semicolons with commas before splitting: =TRIM(TEXTSPLIT(SUBSTITUTE(A2,";",","),,",",TRUE)). It assumes that neither character appears as part of an item’s actual text. The resulting list contains one item per row.
Be careful with values that contain the delimiter themselves. For example, a plain comma split cannot distinguish the separator in red, green from a comma that belongs inside a name or description. If the source is a properly formatted CSV file with quoted fields, import it through Data > From Text/CSV and check the parsed columns rather than blindly splitting the text on commas. A simple delimiter formula does not interpret CSV quotation rules.
Split a whole dataset with Power Query
For many source rows, or when the transformation must be repeated, Power Query is generally more manageable than writing a separate spill formula for every cell. It can split a column into rows while carrying other columns, such as an order number or customer name, into each resulting row.
- Make sure the source data has headers and is arranged in a continuous range. Select a cell in the range and choose Data > From Table/Range. If Excel asks whether to create a table, confirm the range and select OK.
- In the Power Query Editor, select the column containing the delimited values.
- Choose Home > Split Column > By Delimiter. Select or enter the separator used in the source data.
- Expand Advanced options and choose Split into Rows, then confirm the dialog.
- Review the preview. Check that each item has its own row and that related fields, such as IDs and dates, remain associated with the right items.
- Choose Home > Close & Load to return the result to Excel.
Power Query is useful because the steps are recorded. If the source table is later updated, refresh the query to apply the same transformation to the new data. The exact update process depends on how the source is connected, but a normal worksheet table used as the query source can be refreshed from the query’s context menu or the Data tab.
Before splitting, consider duplicating a column that you want to preserve in its original form. Also retain a unique record ID if the output will be used for analysis. If order 104 contains three products, the transformed table should have three rows with order ID 104 repeated on each one. Without that repeated ID, it may be difficult to tell which product entries belonged to the same order.
Power Query is included in current Microsoft 365 Excel for Windows. Power Query features and interface details can differ by Excel version and operating system; on Mac, availability and supported connectors may vary. If the listed commands are missing, check the Excel version and use a formula or a manual approach available in that installation.
Use Text to Columns when the split is a one-time task
Text to Columns splits data horizontally, not vertically, but it can still help with a one-off conversion. Select the cell or cells, choose Data > Text to Columns, select Delimited, choose the delimiter, and finish the wizard. Excel places the pieces in neighboring columns. Copy those resulting cells, then use Home > Paste > Transpose in a clear location to stack them into rows.
This approach is handy when you do not have TEXTSPLIT or do not need a refreshable query. It requires an extra transpose step and more care with the destination range. Check that the cells to the right of the source are empty before running Text to Columns, because the operation can overwrite existing content in its output area. Keep a copy of the original data until you have verified the result.
Another option, if Text to Columns has already produced values across a row, is the TRANSPOSE function. With the split values in B2:D2, enter =TRANSPOSE(B2:D2) in a clear cell outside a table. The three values spill vertically in modern Excel. This is a second-stage conversion; TRANSPOSE does not itself identify or split the original delimiter-separated text.
Keep related information and clean the results
A split can change the shape of a dataset, so think about what each row should represent before transforming it. If a cell lists several tags for one product, the output may need one row per product-tag combination, with the product ID repeated. If each cell holds several names but there is no record ID, the resulting list may lose important context once separated from its original row.
After splitting, check for leading or trailing spaces, empty items, duplicates, and inconsistent spelling. TRIM removes ordinary extra spaces around text but does not remove every invisible character. In particular, nonbreaking spaces copied from webpages may remain. If an item looks blank but is not treated as blank, use Find and Replace or clean the source before splitting.
Also decide whether an empty item is meaningful. A value such as red,,blue has an empty piece between two commas. Setting TEXTSPLIT’s ignore-empty argument to TRUE removes empty pieces; leaving it FALSE preserves them. In Power Query, inspect the preview and choose how blank values should be handled according to the purpose of the data. Removing blanks may be appropriate for a list of tags, but not if their position represents missing information in a fixed sequence.
Why a split may fail or produce unexpected results
- The formula returns #SPILL!: One or more cells where the results need to go are occupied, merged, or otherwise unavailable. Clear the spill range or move the formula.
- The formula returns #NAME?: The Excel version may not support TEXTSPLIT, or the function name may be misspelled. Use Power Query or Text to Columns in an older version.
- Everything stays in one piece: The delimiter in the formula does not exactly match the character in the source. A regular space, nonbreaking space, comma, and semicolon are different characters.
- Results appear in columns: The formula may have put the separator in TEXTSPLIT’s column-delimiter argument. Leave that argument empty and supply the separator as the row delimiter, as in the examples above.
- Items are cut off or overwritten: Check the destination area before using Text to Columns or pasting a transposed result. Keep a backup of important source data.
When a formula result seems correct but later edits do not appear, remember that a spill formula recalculates from its source cell; do not type into the spilled result cells. To create a static list, copy the results and use Paste Special > Values in a separate location. This breaks the formula link, so later changes to the source will not update that copied list.
Which method should you choose?
Use TEXTSPLIT when you have Microsoft 365 or Excel 2024, want a quick formula result, and are splitting a small number of cells. Use Power Query when you have a repeated task or many records, especially when other columns must stay linked to every split item. Use Text to Columns followed by transpose for a straightforward one-time conversion, particularly in older Excel versions.
These methods are not interchangeable in every situation. A formula gives a live result that updates with its source; Power Query creates a query output that you refresh; and the manual method produces worksheet values that you may need to redo after the source changes. Choose based on how often the data changes, how much context must be preserved, and whether you need the result to update automatically or on refresh.
Frequently Asked Questions
Can Excel split a cell into rows automatically?
Yes. In Microsoft 365 or Excel 2024, TEXTSPLIT can return a vertical spill range when its row-delimiter argument is specified. For recurring transformations across a table, Power Query can split the selected column into rows and be refreshed when the source changes.
How do I split text at a line break?
With the text in A2, use =TEXTSPLIT(A2,,CHAR(10),TRUE) in a clear cell. CHAR(10) is the line-feed delimiter. If the source uses a different line-break character or contains extra blank lines, inspect and clean the text if the output is not as expected.
Can I split one cell into rows without TEXTSPLIT?
Yes. Use Data > Text to Columns to separate the values into columns, then copy them and choose Home > Paste > Transpose to place them in rows. For repeatable work across many records, use Power Query if it is available in your Excel version.
Why does TEXTSPLIT show #SPILL!?
Excel cannot place the complete result because something is blocking one or more cells in the spill range. Clear the cells below the formula, unmerge cells in the destination area, or move the formula to a larger empty area. A spill formula cannot return its results inside an Excel Table.
Can I split multiple cells in a column into one combined list?
Yes, but a single TEXTSPLIT formula for one cell is not a complete solution for combining an entire source column. Power Query is usually the clearest option: split the column into rows, and preserve any ID or other fields needed to identify where each item came from.
Related
- Excel Remove Duplicates and Sum Values: A Practical Guide
- Excel Slicer Slider: Range Filters and Alternatives
- Excel Slicer Range: What It Filters and How to Change It
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Conditional Formatting RGB Colors ExplainedYes. In Excel for Microsoft 365 on Windows, you can use a custom RGB color in a conditional…
- Excel Date Difference: Days, Workdays, and Inclusive CountsTo find the number of days between two dates in Excel, subtract the earlier date from the later…
- Calculate Age in Excel From a Birth Date, Step by StepTo calculate someone’s age in completed years in Excel, put their birth date in A2 and enter =DATEDIF(A2,TODAY(),"Y")…
- Excel Shortcut to Insert a Copied RowIn Excel for Microsoft 365 on Windows, copy the entire row, select the row where the copy should…
- Quadrant Scatter Plot in Excel: Create and Format OneTo create a quadrant scatter plot in Excel, insert an XY scatter chart, then add one vertical and…
- Excel Merge Cells and Combine Text: How They DifferIn Excel, merging cells joins a selected area into one larger cell, but it does not combine the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.