Excel Split Text into Rows: 3 Easy Ways

Updated 5 November 2026 · Standard formula, shown below

Yes, you can split text into rows in Excel using the “Text to Columns” feature (with a transpose step), Power Query, or a combination of functions like TEXTSPLIT and TRANSPOSE. This article explains each method step by step, shows you when to use which, and answers common questions about splitting data across rows.

Why Split Text into Rows?

Excel users often receive data where multiple values are crammed into a single cellâ€”like a list of names, email addresses, or product codes separated by commas, semicolons, or spaces. While Excel’s built-in “Text to Columns” is great for splitting into columns, splitting into rows requires a different approach. This is a common need when preparing data for pivot tables, filtering, or database import, where each row should represent a single record.

Method 1: Text to Columns + Transpose (Quick and Simple)

This is the easiest method for one-off tasks and works in all Excel versions.

Select the cell or column containing the text you want to split.
Go to the Data tab and click Text to Columns.
Choose Delimited and click Next.
Select the delimiter (comma, semicolon, space, or other) that separates your values. You can preview the result at the bottom.
Click Finish. The text is now split across columns.
Select all the newly created cells, copy them, right-click on a blank cell, and choose Paste Special â†’ Transpose. This flips the columns into rows.

Note: If you have multiple rows of data, you’ll need to repeat the transpose for each row or use a more automated method like Power Query.

Method 2: Power Query (Best for Repeating or Large Datasets)

Power Query is a data transformation tool built into Excel (Windows and Mac). It’s ideal when you need to split text into rows regularly or when dealing with thousands of rows.

Select your data range and go to Data â†’ From Table/Range. If your data isn’t already a table, Excel will prompt you to create one.
In the Power Query editor, select the column you want to split.
Go to Home â†’ Split Column â†’ By Delimiter.
Choose your delimiter (comma, semicolon, custom) and select Split into Rows (instead of columns).
Click OK. You’ll see the values now appear in separate rows.
Click Close & Load to put the result back into a new worksheet.

This method automatically handles multiple rows and keeps the other columns intact.

Method 3: Using TEXTSPLIT and TRANSPOSE Functions (Dynamic)

If you’re using Excel 365 or Excel 2021, you have access to the TEXTSPLIT function, which makes splitting text into rows a one-formula task.

Syntax: =TEXTSPLIT(A1, ",") splits text in A1 by commas into separate columns. To get rows instead, wrap it with TRANSPOSE:

=TRANSPOSE(TEXTSPLIT(A1, ","))

This spills the results down a column. If you have multiple cells to split, you can combine it with other functions, but it’s easiest to use Power Query for that scenario.

How to Choose the Right Method

One-time, small data: Text to Columns + Transpose is quick and requires no formulas.
Repeated or large data: Power Query is the most robust and repeatable.
Dynamic updates: If your source data changes and you want the split to update automatically, use TEXTSPLIT (Excel 365/2021) or a formula-based approach.

Common Problems and Their Solutions

Problem: After transposing, the data is horizontal, not vertical.
Solution: Make sure you use Paste Special â†’ Transpose. If you see the data still in columns, you may have missed the Transpose option.

Problem: Text to Columns only splits the first cell.
Solution: Text to Columns only works on one column at a time. To split multiple rows, you need to repeat the process for each row or use Power Query.

Problem: The delimiter is not a comma, but a space or semicolon.
Solution: In Text to Columns, you can select “Space” or “Other” and type the delimiter. In Power Query, choose “Custom” and enter the delimiter.

Problem: There are extra spaces or blank cells after splitting.
Solution: Use the TRIM function or in Power Query, use the “Trim” transformation under the Transform tab.

Frequently Asked Questions

Can I split text into rows without using a formula?

Yes, the Text to Columns + Transpose method doesn’t use any formulas. Power Query also doesn’t require you to write formulasâ€”it’s all point-and-click.

Does TEXTSPLIT work in Excel 2019 or earlier?

No, TEXTSPLIT is only available in Excel 365 and Excel 2021. For older versions, use Text to Columns or Power Query.

How do I split text into rows but keep the original data intact?

All the methods above either create a new column/range (Text to Columns) or a new table (Power Query). Your original data remains untouched if you choose to load the result to a new location.

What if my delimiter is a line break (Alt+Enter) inside a cell?

In Text to Columns, you can use the “Other” option and press Ctrl+J to enter a line break as the delimiter. In Power Query, select “Custom” and type #(lf) for a line feed.

Can I split text into rows based on a fixed number of characters?

Yes, in Power Query, use Split Column â†’ By Number of Characters and choose “Once, as far as possible” or “Repeatedly”. In Text to Columns, you can use “Fixed width” and set break lines.

Why does my TEXTSPLIT formula return a #SPILL! error?

This error occurs when there isn’t enough empty space for the results to spill. Clear any cells that block the spill range, or use the @ operator to force a single result.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.