Yes. In Excel for Microsoft 365, enter =TEXTSPLIT(SUBSTITUTE(A2,CHAR(13),""),,CHAR(10),TRUE) in an empty cell to split the line breaks in A2 into a vertical list. For a repeatable solution across many records, use Power Query’s Split Column by Delimiter command and choose to split into rows.
What splitting a multiline cell means
A multiline cell contains several pieces of text separated by line breaks, even though they all appear in one worksheet cell. For example, A2 might display three names on separate lines: “Mia,” “Noah,” and “Sam.” Splitting the cell into rows puts each name in its own cell down a column. This is different from wrapping text: wrapping changes how text appears inside a cell but does not create separate values.
Excel typically stores a manual line break inside a cell as a line-feed character, represented in formulas by CHAR(10). Line breaks can come from typing Alt+Enter in a cell, copying text from another program, or importing data. Knowing that the separator is a character helps explain why a formula or a query can split the value even when the break is not visible in the formula bar.
Use TEXTSPLIT for one cell
For the formula above, the example assumes the multiline text is in A2. Put the formula in B2, or in another empty cell with enough blank cells below it. Excel spills the results vertically, one item per row. If A2 contains “Mia” followed by a line break, “Noah,” another line break, and “Sam,” the spill range returns Mia, Noah, and Sam in three consecutive cells.
The formula removes carriage returns with SUBSTITUTE before splitting at each line feed. This accommodates text that uses a carriage-return/line-feed pair, as can happen with copied or imported text, as well as ordinary Excel line breaks. The final TRUE argument tells TEXTSPLIT to ignore empty items. If the cell contains a blank line between two names, that empty line will not create a blank result row.
- Select an unused cell beside or below the source data.
- Enter the formula, changing A2 if the source is in a different cell.
- Press Enter and check the cells below the formula for the spilled results.
- Copy the results and use Home > Paste > Paste Values if you need a fixed list rather than a formula-linked result.
TEXTSPLIT is available in Microsoft 365 and Excel 2024. It is not available in every older Excel release. The formula works in Excel for Mac when that version includes TEXTSPLIT; the formula itself does not need a different Mac syntax. In Excel for the web, availability also depends on the version and account features.
Split many records with Power Query
Power Query is usually the better choice when a column contains many multiline cells and you want each original record transformed consistently. It can split one column into rows while repeating the values in other columns, such as an ID or category. The query can also be refreshed when the source table changes, instead of manually copying a formula for each record.
- Place the source data in a range with column headers, then select a cell in it and choose Data > From Table/Range. Confirm that the correct range and headers are selected.
- In Power Query Editor, select the column that contains the multiline text.
- Choose Transform > Split Column > By Delimiter. Select Custom as the delimiter. In the delimiter field, press Ctrl+J to enter a line-feed character; it may look as if the field is blank.
- Open Advanced options and select Split into Rows. Choose the appropriate split setting, usually Each occurrence of the delimiter, and confirm.
- Review the preview, then select Home > Close & Load to return the transformed data to Excel.
Power Query repeats the other columns’ values for each split item, so a source row such as ID 104 with “Mia” and “Noah” becomes two output rows, both carrying ID 104. Keep the source table intact if you expect to refresh the query. Power Query creates a transformed output; it does not need to overwrite the original cells. Power Query features and menu details can vary between Excel for Windows and Mac, so on a Mac check the available commands in the Data tab for your installed build.
Choose a method for your workbook
The right method depends on whether this is a one-off cleanup, a live formula result, or a recurring data-import task. Consider these practical differences:
- TEXTSPLIT: Fast for a few cells or a formula-driven worksheet. Results update when the source changes, but the spill range must remain clear.
- Power Query: Suited to larger tables, repeated imports, and cases where other columns must stay associated with each item. Refresh the query after changing the source.
- Manual copy and paste: Reasonable for a tiny, one-time list, but easy to mis-handle when cells contain different numbers of lines.
For just one multiline cell, the formula is generally the shortest route. For a full table with hundreds of source rows, a query is easier to audit and repeat. If you need to send a fixed list to someone, paste the formula results as values after checking them. If the output should keep changing along with the source, keep the formula instead.
Working with several source cells
A common complication is that each source cell can contain a different number of lines. A formula entered beside one source cell spills down, so filling the same formula into the next row may cause overlapping spill ranges. Excel cannot place one spill array on top of another. If a formula displays a #SPILL! error, check whether cells in its intended output range contain data, merged cells, or another formula.
For a small number of source cells, split each one into its own clear area, or copy a formula result and paste values before processing the next cell. For a long table where all items need to form one normalized list, use Power Query: splitting into rows naturally accommodates different item counts and preserves related columns. Avoid assuming that every source record has the same number of lines unless you have checked the data.
Blank lines, spaces, and other common problems
Unexpected blank results or a formula that does not split usually point to the source delimiter or output area. Check these issues before rebuilding the worksheet:
- The formula returns the whole text: The source may use a different separator, such as a semicolon, or contain a character other than a line feed. If the data came from an external system, inspect a sample and test the delimiter used by that source.
- The formula shows #SPILL!: Clear the cells where the results should appear. Also check for merged cells in that range.
- Empty items appear: The formula’s final TRUE argument skips empty pieces. If you need to preserve intentional blank lines, use FALSE instead and review how those empty results affect the downstream data.
- Items have unwanted spaces: TEXTSPLIT separates at the line break but does not remove leading or trailing spaces from each item. Review and clean the results if exact text matching matters.
- Power Query finds no delimiter: Recheck that you entered a line feed with Ctrl+J in the custom delimiter field and selected Split into Rows, not Split into Columns.
A line break is not the same as a comma or a space. Replacing every line break with a comma may make a readable single string, but it does not create separate worksheet rows. Likewise, Text to Columns is designed to divide content across columns; it is not the direct command for turning one cell’s lines into rows.
Keep the output useful and reliable
Before splitting important data, decide what should happen to blank lines, duplicates, and the original record. A blank line might be accidental formatting, or it might indicate a meaningful missing item. The formula shown skips empty entries, while Power Query may produce rows that require a follow-up filter depending on the source and query steps. Inspect a few results against the original before relying on the transformed list.
Also preserve a relationship to the source where necessary. If the multiline cell belongs to an order number, employee, or product, include that identifier in the output. Power Query repeats other column values automatically during a split into rows. With a formula-only workflow, keep a separate identifier column or make a copy of it for every resulting item. Otherwise, a clean-looking list can lose the context needed to interpret it.
Finally, be careful when replacing original data. A formula spill is a view of a result, not a destructive edit to the source. Power Query also loads transformed output separately by default. Keep the original table until you have verified the split, especially when the source is imported or difficult to reconstruct.
Frequently Asked Questions
Can Excel split a cell into rows automatically?
Yes. TEXTSPLIT can spill one cell’s line-separated items into rows in supported Excel versions. Power Query can split a column into rows for a larger table.
What formula splits a cell at line breaks?
With the multiline text in A2, use =TEXTSPLIT(SUBSTITUTE(A2,CHAR(13),""),,CHAR(10),TRUE). It removes carriage returns, splits at line feeds, and ignores empty items.
Can I split multiple cells with different numbers of lines?
Yes, but separate formula spill ranges can overlap. For a table of records with varying line counts, Power Query is generally more practical because it creates rows for each item and can repeat associated columns.
Does Text to Columns split a cell into rows?
No. Text to Columns divides text across columns. Use TEXTSPLIT with a row delimiter or Power Query’s Split into Rows option for a vertical result.
Will the results update if I edit the source?
A TEXTSPLIT formula recalculates when its source cell changes. Power Query output updates when you refresh the query. Pasted values do not update automatically.
What if my Excel does not recognize TEXTSPLIT?
Your Excel version may not include the function. Use Power Query if it is available in your installation, or use a manual method for a very small one-time list. Check your Excel version before choosing a formula-based workflow.
Related
- Excel Remove Slicer: How to Delete or Clear One
- Excel Remove Everything but Duplicates: Keep Repeats
- Excel Remove Duplicates Not Available? Fixes to Try
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel: Make Rows Collapsible with Grouping and OutlinesYes. In Excel for Microsoft 365 on Windows, select the rows you want to fold away, then choose…
- Inventory Spreadsheet Template for Google SheetsFor an inventory spreadsheet, use Google Sheets rather than Google Docs: Sheets is Google’s spreadsheet app, while Docs…
- Currency Exchange in Google Sheets: Rates and FormulasTo get a currency exchange rate in Google Sheets, enter a formula such as =GOOGLEFINANCE("CURRENCY:USDEUR"), which requests the…
- Excel Slicer Colors: How to Change and Customize ThemTo change Excel slicer colors in Microsoft 365 for Windows, select the slicer, open the contextual Slicer tab,…
- Excel Table Reference Syntax: A Practical GuideExcel table references use a table name and, usually, a column name in square brackets. For example, with…
- Excel Merge Across vs. Merge Cells: Key DifferencesMerge Across makes a separate merged cell for each row in your selection, while Merge Cells combines the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.