To split text in one Excel cell into separate rows, enter a formula such as =TEXTSPLIT(A2,,",") in an empty cell; in Microsoft 365 or Excel 2024, it spills each comma-separated item down a column. For a repeatable process that also keeps related columns with each item, use Power Query: select the column, choose Transform > Split Column > By Delimiter, then choose Split into Rows.
What “text to rows” means in Excel
“Text to rows” usually means taking a cell that contains several values separated by a delimiter and turning those values into individual cells down a column. For example, a cell containing red, green, blue can become three rows: red, green, and blue. The delimiter is the character or text between values—in this example, a comma.
This is different from Excel’s Text to Columns command. Text to Columns divides content across columns, so the example would initially become three side-by-side cells. It does not have a direct “split into rows” option. If your goal is a vertical list, use TEXTSPLIT with a row delimiter, Power Query’s Split into Rows option, or a transpose workaround.
It is also different from wrapping text. Home > Wrap Text changes how content appears inside a cell but leaves all the text in that one cell. Splitting creates separate values that you can sort, filter, count, or use in other calculations.
Choose the right method
The best approach depends on whether you need a quick result, a live formula, or a transformation you can repeat. Consider how many source cells you have, whether the source data changes, and whether other columns need to be repeated alongside each split value.
- Use TEXTSPLIT for a quick, formula-driven result from one or a few cells. The output updates when the source text changes.
- Use Power Query for lists or tables that you will split again after refreshing source data, especially when each item needs to stay connected to fields such as a customer or order number.
- Use Text to Columns and transpose when you need a one-time result and prefer ribbon commands over a formula or query.
For a large dataset, Power Query is generally easier to manage than applying a separate manual operation to every record. For a small, changing list, a formula is often faster to set up.
Split a cell vertically with TEXTSPLIT
In Excel for Microsoft 365 or Excel 2024, TEXTSPLIT can use a row delimiter to return items down the sheet. Suppose A2 contains red, green, blue. With that source layout, enter this formula in B2:
=TEXTSPLIT(A2,,",")
The formula returns red in B2, green in B3, and blue in B4. The second argument is left blank because no column delimiter is being specified; the comma is supplied as the third argument, the row delimiter. Leave the cells below B2 clear so Excel can place the spilled results there. If any destination cell is occupied, Excel may show a #SPILL! error.
Spaces after delimiters are common in human-entered lists. With red, green, blue, a basic split can leave a leading space before green and blue. If the text is in A2 and the comma is the delimiter, this version trims those spaces from the returned items:
=TRIM(TEXTSPLIT(A2,,","))
The result is still three vertical values, now without the extra spaces. TEXTSPLIT also has an ignore_empty argument. With values in A2 such as red,,blue, the following formula skips the empty item between the consecutive commas:
=TEXTSPLIT(A2,,",",TRUE)
TEXTSPLIT is a dynamic-array function, so one formula produces multiple results; you do not fill it down like a conventional formula. It is not available in every older Excel release. If Excel does not recognize the function, use Power Query or the Text to Columns workaround instead. Dynamic-array formulas also cannot spill inside an Excel table, so place the formula outside the table or use a query.
Split values into rows with Power Query
Power Query is useful when the source is a table and the split needs to be repeatable. It can split a delimited column into multiple records and carry the other columns into each new record. For instance, if an order row has an order ID and a cell listing three products, splitting the product cell into rows can produce three rows with the same order ID and one product on each row.
- Put the source data in a table, or select a cell in a contiguous data range. If needed, use Insert > Table and confirm whether the data has headers.
- Choose Data > From Table/Range. Excel opens the Power Query Editor.
- Select the column containing the delimited text. In the editor, choose Transform > Split Column > By Delimiter.
- Choose or enter the delimiter, such as a comma, semicolon, or a specified character. Expand Advanced options and select Split into Rows, then confirm.
- Check the preview, including the repeated values in other columns. Choose Home > Close & Load to return the result to Excel.
When the source data changes, refresh the query to rebuild the output rather than repeating the split manually. Power Query usually loads the result separately from the original source, so keep the source and output areas distinct. The editor and available connectors can differ between Windows, Mac, and web versions of Excel; if the described command is unavailable in your version, TEXTSPLIT is a practical alternative for a simple split.
Use Text to Columns, then transpose
Text to Columns is useful when the values are currently arranged in a row and you want a quick, one-time conversion. On Excel for Microsoft 365 for Windows, select the cell or column and choose Data > Text to Columns. Select Delimited, choose Next, select the delimiter, and choose Finish. The separated values appear across columns.
To turn that horizontal result into a vertical list, copy the separated cells, select a blank destination cell, and use Home > Paste > Transpose. Transpose swaps rows and columns. Check the result before deleting the original data, particularly if the split values were next to other information. Text to Columns can overwrite cells to the right, and Paste Special may not preserve every formula or formatting detail exactly as expected.
This route is less convenient when each of many source rows contains a different list. It is a manual sequence, not a refreshable transformation. On Mac, the Data tab includes Text to Columns in supported Excel versions, though ribbon placement and dialog details may vary. In Excel for the web, available import and transformation features can differ from desktop Excel.
Keep each split value connected to its record
Splitting a list changes the shape of your data, so consider what each resulting row represents. If a row contains a person’s name, an ID, and several skills in one cell, the useful output may be one row per person-skill pair—not simply a detached list of skills. Power Query is designed to retain the other columns as it creates those rows.
A formula that splits just one cell returns only its split values. It does not automatically attach an adjacent customer ID to every result. For a small task, you can copy the associated value alongside the spilled results, but a formula-based solution for many records needs more deliberate design. For structured data that must remain aligned across many columns, use Power Query and verify that the repeated identifiers are correct.
Before splitting, decide whether duplicates should remain. If a cell contains apples, apples, pears, a split preserves both occurrences. That may be right for a record of selections, even if it looks redundant. Removing duplicates is a separate data-cleaning decision, not an automatic part of splitting.
Common problems and how to fix them
- Everything stays in one result: Check that the delimiter matches the source exactly. A comma, semicolon, tab, and line break are different characters.
- Extra spaces appear: Use TRIM around the TEXTSPLIT result for ordinary spaces around values. Review the source if it contains unusual whitespace.
- Blank rows appear: Repeated delimiters or a delimiter at the start or end can create empty items. In TEXTSPLIT, set
ignore_emptyto TRUE when dropping those items is appropriate. - Excel shows #SPILL!: Clear the cells where the formula’s results need to appear. Also check for merged cells or a formula placed inside an Excel table.
- A delimiter occurs inside a value: A simple split may break that value incorrectly. For example, a comma inside a quoted address is not necessarily a separator. Clean or restructure the data, or use a source-aware import process that understands quoting.
- Line breaks do not split as expected: The source may use a different line-break character than expected. Inspect or clean imported text before choosing the line break as the delimiter.
Keep an untouched copy of the original data until you have checked the number of output records and sampled several results. A split can look plausible while still producing an incorrect list if the wrong delimiter was selected.
Frequently Asked Questions
Can Excel split text directly into rows?
Yes. In Microsoft 365 or Excel 2024, use TEXTSPLIT with the separator as its row delimiter, such as =TEXTSPLIT(A2,,","). In Power Query, choose Split into Rows in the delimiter dialog.
Why does Text to Columns split into columns instead of rows?
That is what the command is designed to do: it distributes separated values horizontally. To get a vertical result, use TEXTSPLIT or Power Query, or transpose the Text to Columns output.
Can I split a cell at each line break?
Yes. If A2 contains line-break-separated text, use =TEXTSPLIT(A2,,CHAR(10)) in an empty cell. This uses the line-feed character as the row delimiter. Imported text can contain different line-break characters, so check the output if some items remain joined.
Will the split results update when I edit the original cell?
TEXTSPLIT recalculates when its source cell changes, provided the spilled output area remains clear. Power Query output changes when you refresh the query. A manual Text to Columns and transpose operation does not stay linked to its source.
How do I split a comma-separated cell and remove spaces?
If the source text is in A2, use =TRIM(TEXTSPLIT(A2,,",")). It splits on commas and trims ordinary leading and trailing spaces from the returned values.
Related
- Conditional Formatting: Highlight Duplicates in Excel
- Data Validation Testing: Methods and Examples
- Data Validation Studio: Excel Rules, Setup, and Limits
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Remove Duplicates in Excel by Entire Row: A Safe GuideTo remove duplicate records in Excel, select the full data range, choose Data > Remove Duplicates, and check…
- Create Labels in Word from Excel: Step-by-Step GuideTo create labels in Word from an Excel sheet, prepare your recipient list with one row per label,…
- Consolidate Values in Excel: Steps and OptionsTo consolidate values in Excel, select the destination cell, choose Data > Consolidate, select a summary function such…
- Edit a Dropdown List in Excel: Sources, Steps, and FixesTo edit a dropdown list in Excel for Microsoft 365 on Windows, select a cell that uses it,…
- Camping Meal Planner Spreadsheet: Plan Meals and Pack FoodA camping meal planner spreadsheet helps you map meals to each day, estimate how much food to bring,…
- Track Changes in Word: How to Review and Accept EditsTo use Track Changes in Microsoft Word, open the Review tab and select Track Changes; Word will mark…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.