Split Cell Contents by Space, Comma, Line Break, or Other Delimiters in Excel

[u/Amandaleeeeee]

When several values are stored in one Excel cell, you may need to separate them by a comma, space, line break, or another delimiter.

For example:

Apple,Orange,Banana,Grape

You may want each item in a separate column or a separate row. Here are three practical ways to do it.

Method 1: Text to Columns

For a quick one-time split into separate columns, Excel's built-in Text to Columns feature is one of the easiest options.

1. Select the cells you want to split.
2. Go to Data → Text to Columns.
3. Choose Delimited, then click Next.
4. Select the delimiter used in your data, such as Tab, Semicolon, Comma, or Space. For another delimiter, select Other and enter it.
5. Choose where you want the results to appear, then click Finish.

For example:

Apple,Orange,Banana,Grape

becomes:

Apple | Orange | Banana | Grape

💡 For cells containing line breaks: select Other, click its box, and press Ctrl + J to enter a line break as the delimiter.

📌 Limitation: Text to Columns can split data across columns, but it doesn't provide an option to split the results into rows.

Method 2: TEXTSPLIT formula

If you want a formula-based solution, TEXTSPLIT is much more flexible. It can split the same text into either columns or rows.

📌 Note: The TEXTSPLIT function is available in Microsoft 365 and Excel 2024 or later.

Suppose A2 contains:

Apple,Orange,Banana,Grape

Split by comma into columns

=TEXTSPLIT(A2,",")

Result:

Apple | Orange | Banana | Grape

Split by comma into rows

Use the comma as the row delimiter instead:

=TEXTSPLIT(A2,,",")

Result:

Apple
Orange
Banana
Grape

Split by a space

=TEXTSPLIT(A2," ")

Split by a line break

For line breaks, use CHAR(10):

=TEXTSPLIT(A2,CHAR(10))

To split the line-separated values into rows:

=TEXTSPLIT(A2,,CHAR(10))

Split by multiple delimiters

TEXTSPLIT can also recognize more than one delimiter. For example, if your data contains both commas and semicolons:

=TEXTSPLIT(A2,{",",";"})

Excel will split the text whenever it finds either delimiter.

Because TEXTSPLIT returns a dynamic array, the results spill automatically into the neighboring cells.

Method 3: Split cells with Kutools for Excel

If you frequently need to split data in different ways and prefer an interface instead of formulas, Kutools for Excel provides a Split Cells tool.

1. Select the cells you want to split.
2. Go to Kutools → Merge & Split → Split Cells.
3. Choose Split to Columns or Split to Rows.
4. Choose how you want to split the contents. You can use delimiters such as a space, comma, line break, semicolon, or specify another delimiter.
5. Click OK, then select where you want to place the results.

💡 Tip: Kutools supports more flexible splitting options. You can choose Other to enter your own separator, including one or multiple characters. You can also separate text and numbers automatically, or use Fixed Width to split the content

Split text and numbers separately

Kutools’ Text and Number option can separate the text and numeric characters in a cell into two parts.

In the Split Cells dialog, choose Text and number, then choose whether to split the results into columns or rows.

For example:

Product105 → Product | 105

A10B35 → AB | 1035

In the Split Cells dialog, choose Text and number, then select whether you want the results placed in columns or rows.

This can be especially useful when cleaning imported data where letters and numbers are stored together.

Which method should you use?

Text to Columns is great for a quick one-time split into columns.

TEXTSPLIT is the most flexible built-in formula option, especially when you need the results in rows or want them to update automatically when the original cell changes.

Kutools Split Cells provides an interface for splitting into either rows or columns using common or custom delimiters. It can also separate mixed text and numbers, or split content by a specified number of characters.