How to Split Cells in Excel (Text to Columns)

To split cells in Excel, select the column and use Data → Text to Columns — it separates one cell’s contents into multiple columns using a delimiter like a comma or space. Excel can’t truly split a single cell in place, so the data has to move into adjacent columns (or down into rows). Here’s every method, and which to use.

Which method should you use?

Text to Columns is the workhorse. Start there unless your data is irregular.

Method 1: Text to Columns (the standard way)

Say column A holds "Doe, John" and you want the last name in one column and the first in another.

1. Insert empty columns to the right — Text to Columns overwrites whatever is there.
2. Select the column you want to split (click the letter at the top).
3. Go to Data → Text to Columns.
4. Choose Delimited → Next.
5. Tick your separator — Comma, Space, Tab, Semicolon, or type a custom one.
6. Check the Data preview at the bottom to confirm it looks right.
7. Click Finish.

The contents split into the adjacent columns immediately.

Watch the overwrite. If column B had data, it’s gone. Always insert blank columns first — or work on a copy.

Splitting by a fixed width instead

If every entry is the same length (like a product code ABC-12345), choose Fixed width at step 4 instead, then click in the preview to place the break lines manually. Useful for codes, IDs, and legacy exports.

Method 2: Flash Fill (for messy data)

Flash Fill learns the pattern from one example — no settings, no formulas.

1. In the column next to your data, type the result you want for the first row.
2. Press Ctrl + E.
3. Excel fills the rest by detecting the pattern.

Example: if A2 is "John Smith" and you type John in B2, Flash Fill pulls every first name down.

Watch out: Flash Fill is a one-time action, not a formula. If the source changes, the result doesn’t update. It’s also unreliable on very irregular data — always eyeball the output.

Method 3: Formulas (updates automatically)

If your source data changes and you want the split to follow, use formulas.

Modern Excel (Microsoft 365) — one formula does it all:

=TEXTSPLIT(A2, " ")

That splits A2 on every space into as many columns as needed. Change the delimiter to "," for commas.

Older Excel — split with LEFT/RIGHT/FIND:

First name:  =LEFT(A2, FIND(" ", A2) - 1)
Last name:   =RIGHT(A2, LEN(A2) - FIND(" ", A2))

These find the space and cut either side of it. They don’t update as gracefully as TEXTSPLIT but work in every version.

Google Sheets: use Data → Split text to columns (same idea), or the SPLIT() function.

Split a cell into rows instead of columns

If one cell contains several items separated by commas ("Red, Green, Blue") and you want them stacked vertically:

• Microsoft 365: =TEXTSPLIT(A2, , ",") - the empty middle argument is the column delimiter, so the comma splits down into rows.
• Older Excel: use Data → From Table/Range in Power Query, then split the column by delimiter and choose Rows.
• Google Sheets: =TRANSPOSE(SPLIT(A2, ", ")).

Common problems

Before you split: a quick safety check

1. Copy the column to a scratch area, or work on a duplicate sheet.
2. Check the delimiter is consistent — scroll to the bottom, not just the first ten rows.
3. Insert blank columns so nothing is overwritten.
4. Watch dates and leading zeros — Excel reinterprets them as numbers.

Once your data is split, how to remove duplicates in Excel is often the next step, and how to sort in Excel lets you order the new columns. If you’re still getting to grips with the basics, see Google Sheets formulas for beginners.

FAQ

How do I split one cell into two in Excel?

Select the column, go to Data → Text to Columns, choose Delimited, pick your separator, and click Finish. Insert blank columns first so nothing is overwritten.

Can Excel split a cell without moving the data?

No — Excel can’t split a cell in place, unlike a table in Word. The contents must go into adjacent columns or rows.

How do I split a cell by a comma in Excel?

Use Data → Text to Columns, choose Delimited, and tick Comma (plus Space if there’s a space after the comma). Check the preview before clicking Finish.

What’s the difference between Text to Columns and Flash Fill?

Text to Columns splits at a consistent delimiter and overwrites adjacent columns. Flash Fill (Ctrl + E) learns from one example and handles messy data — but it’s a one-time result, not a live formula.

How do I split text in Excel that updates automatically?

Use a formula — =TEXTSPLIT(A2, " ") in Microsoft 365, or LEFT/RIGHT with FIND in older versions. Unlike Text to Columns, formulas recalculate when the source changes.