Split a cell into rows in Excel

Splitting a cell into rows in Excel means taking a cell with a list — “carrot, onion, potato” — and putting each value on its own row. For one cell, the formula =TEXTSPLIT(A2,,", ") is enough in Excel 365 or 2024. If you need to split a whole column and keep each value’s category next to it, use Power Query or the “Unpivot and split 2nd column” procedure of the !SEMTools add-in: select two columns, enter the delimiter, and the table is unpivoted onto a new sheet.

The Text to Columns wizard can’t help here: it spills parts only to the right. Below are all working methods, from simple to powerful.

One cell into rows: the TEXTSPLIT function

The TEXTSPLIT function has a third argument — the row delimiter. Leave the second argument empty and the parts go down instead of right.

B2fx=TEXTSPLIT(A2,,", ")Copy

	A	B
1	List	Values
2	carrot, onion, potato	carrot=TEXTSPLIT(A2,,", ")
3		onion
4		potato

The two commas in a row are the skipped column delimiter. The delimiter “, ” includes the space, so the values don’t start with a space.

If the values in the cell are separated by line breaks (Alt+Enter), the delimiter is CHAR(10): =TEXTSPLIT(A2,,CHAR(10)).

A table with categories into rows: Excel 365 formula

It gets harder when there are many cells and each has a “key” in the next column — a category, SKU or customer name — that must repeat on every new row. In Excel 365 and 2024 one formula does it: each comma is replaced with “;key|”, the rows are joined with TEXTJOIN, and TEXTSPLIT cuts the result into rows and columns at once.

D2fx=TEXTSPLIT(TEXTJOIN(";",TRUE,A2:A4&"|"&SUBSTITUTE(B2:B4,", ",";"&A2:A4&"|")),"|",";")Copy

	A	B	C	D	E
1	Category	Values		Category	Value
2	Vegetables	carrot, onion, potato		Vegetables=TEXTSPLIT(TEXTJOIN(";",TRUE,A2:A4&"|"&SUBSTITUTE(B2:B4,", ",";"&A2:A4&"|")),"|",";")	carrot
3	Fruit	apple, pear		Vegetables	onion
4	Berries	raspberry		Vegetables	potato
5				Fruit	apple
6				Fruit	pear
7				Berries	raspberry

For “Vegetables”, SUBSTITUTE turns the list into “carrot;Vegetables|onion;Vegetables|potato”, and “Vegetables|” is added in front. All rows are joined with “;”, and TEXTSPLIT splits them: “;” into rows, “|” into two columns. The data itself must not contain “|” or “;”. TEXTJOIN’s result is limited to 32,767 characters — for large tables use Power Query or the add-in.

Split a cell into rows with Power Query

Power Query is built into Excel 2016 and later and has no length limit. Steps:

1. Click in the table and choose Data → From Table/Range. Excel offers to format the data as a table — accept.
2. In the Power Query editor, select the column with the lists and click Home → Split Column → By Delimiter.
3. Choose the delimiter (for example, Custom “, ”), expand Advanced options and select Split into: Rows.
4. Click Home → Close & Load. The result appears on a new sheet.

The upside: you can refresh the query (Data → Refresh All) when the source table changes. The downside: many steps for a one-off task, and leftover spaces after splitting need a separate Transform → Format → Trim step.

“Unpivot and split 2nd column” in !SEMTools

In the !SEMTools add-in, the same task takes two clicks in any Excel version from 2010:

1. Select two columns: the key on the left (category, SKU, customer), the cells with lists on the right.
2. On the !SEMTools tab open CHANGE → Range → Fast pivot table → Unpivot and split 2nd column.
3. Enter the delimiter exactly as it appears in the cells, including the space if there is one: “, ” or “;”.
4. Click OK. The add-in creates a new sheet with “key — value” pairs, one per row.

E2fxcarrotCopy

	A	B	C	D	E
1	Source sheet: A	B		New sheet: A	B
2	Vegetables	carrot, onion, potato		Vegetables	carrot
3	Fruit	apple, pear		Vegetables	onion
4	Berries	raspberry		Vegetables	potato
5				Fruit	apple
6				Fruit	pear
7				Berries	raspberry

Left: the selected columns. Right: what you get on the new sheet with the delimiter “, ”. The source data is not changed.

A few things to know about the procedure:

• select exactly two columns, otherwise the add-in asks you to;
• the delimiter is matched exactly: if the cells say “carrot, onion” and you enter “,” without the space, the values will start with a space — removing extra spaces fixes it;
• two delimiters in a row produce an empty value between them — filter it out;
• you can include the header row: the header is simply copied to the new sheet as the first row.

The reverse operation — joining the values of one category back into one cell — is in the same menu: Fast pivot table → Join → “Pivot-concatenate (input delimiter)”. More about the fast pivot table — in the article on counting unique values.

Which method to choose

	TEXTSPLIT	TEXTJOIN formula	Power Query	!SEMTools
Keeps the key on every row	no	yes	yes	yes
Updates with the data	yes	yes	on Refresh	no
Size limit	one cell	32,767 characters	none	none
Excel versions	365, 2024	365, 2024	2016+	2010+
Steps	1 formula	1 formula	4–5	2 clicks

Related tasks

• How to split a cell in Excel — into columns, names, diagonally, merged cells.
• Split a sheet by column value — move table rows onto separate sheets.
• Combine all columns into one — when values are spread across columns and you need one list.
• Separate text and numbers inside a cell.

Why a cell won’t split into rows: common mistakes

• Text to Columns only spills to the right. Use the methods in this article for rows.
• TEXTSPLIT returns #SPILL!. There are filled cells below the formula — clear room for the result.
• TEXTSPLIT returns #NAME?. Excel 2021 and earlier don’t have it — use Power Query or the add-in.
• The TEXTJOIN formula returns #VALUE!. The joined text is longer than 32,767 characters — split the table into parts or use Power Query.
• Values start with a space. The delimiter has no space while the data does: use “, ”, not “,”.
• Nothing is split. The cells use another delimiter, e.g. a line break: use CHAR(10).

Split a cell into rows: FAQ

How do I split a cell into multiple rows in Excel?

For one cell, use =TEXTSPLIT(A2,,", ") (Excel 365 and 2024). For a table, use Power Query (Split Column → By Delimiter → Split into Rows) or “Unpivot and split 2nd column” in !SEMTools.

Can Text to Columns split a cell into rows?

No, the wizard spills parts only into columns. You could split into columns and then transpose, but for a table with keys that’s impractical.

How do I keep the category on every row after splitting?

Use Power Query, the “Unpivot and split 2nd column” procedure in !SEMTools or the Excel 365 formula with TEXTSPLIT and TEXTJOIN from this article.

How do I split a cell by line breaks?

The line break delimiter is CHAR(10): =TEXTSPLIT(A2,,CHAR(10)).

Summary: how to split a cell into rows

Task	Method
One cell	=TEXTSPLIT(A2,,", ")
By line breaks	=TEXTSPLIT(A2,,CHAR(10))
Table with categories, Excel 365	TEXTSPLIT + TEXTJOIN + SUBSTITUTE
Large table with refresh	Power Query → Split into Rows
Fast, any version	!SEMTools → Unpivot and split 2nd column

I hope you found what you were looking for on this page and managed to split your cells into rows. To split into columns, see how to split a cell in Excel, and all arguments of the function are covered on the TEXTSPLIT page.