How to Split Cells in Excel

Method 1: Text to Columns

The standard tool, documented by Microsoft as the Convert Text to Columns wizard.

1. Insert enough blank columns to the right of the one you are splitting — one fewer than the number of pieces you expect. Splitting into three parts needs two blank columns.
2. Select the column of text.
3. Data → Text to Columns.
4. Choose Delimited and click Next.
5. Tick the character that separates your data — Space, Comma, Tab, or Other for anything else.
6. Click Finish.

Choose Fixed width instead of Delimited when the data has no separator but the pieces line up in consistent positions — common with older system reports. You then click on the ruler to set the break points.

Method 2: Flash Fill

Excel infers the pattern from an example you type.

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

It is excellent for irregular data that no single delimiter handles — extracting a domain from an email address, or initials from a name.

Two cautions. Flash Fill produces static values, not formulas, so it does not update when the source changes. And it guesses: on inconsistent data it can guess wrong, so check the results rather than assuming. Typing a second example before pressing Ctrl + E improves the guess considerably.

Method 3: formulas, when it must stay live

Use these when the source data will change and the split must follow it.

Splitting a full name in A2:

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

FIND locates the position of the first space. LEFT takes everything before it; RIGHT takes everything after.

The middle-name problem. On "Mary Jane Watson", that last-name formula returns "Jane Watson" — because FIND finds the first space, not the last. To take only the final word:

=TRIM(RIGHT(SUBSTITUTE(A2, " ", REPT(" ", 100)), 100))

That replaces each space with 100 spaces, takes the rightmost 100 characters, and trims. It looks strange but is the standard, reliable way to grab the last word regardless of how many spaces precede it.

In newer versions of Excel, =TEXTAFTER(A2, " ", -1) does the same thing far more readably — the -1 means "the last occurrence".

Method 4: TEXTSPLIT (newer Excel)

If your version has it, this is the cleanest option — one formula spills the results across the columns automatically:

=TEXTSPLIT(A2, " ")

It handles multiple delimiters and can split into rows as well as columns. It is only available in recent versions, so avoid it in workbooks that will be opened in older Excel, where it returns #NAME?.

Splitting one cell into multiple rows

A different problem, and none of the above solves it. Text to Columns produces columns, not rows.

Use Power Query: select the data, Data → From Table/Range, then in the editor use Split Column → By Delimiter, and under Advanced options choose Rows. Load the result back to the sheet.

The step is saved, so a refresh re-applies it to next month's file — worth the extra setup for anything recurring.

Choosing a method

Situation	Use
One-off, clear separator	Text to Columns
One-off, irregular pattern	Flash Fill (Ctrl + E)
Must update as the source changes	Formulas, or TEXTSPLIT
Names with optional middle names	TEXTAFTER, or the REPT trick
Into rows rather than columns	Power Query
Repeating monthly import	Power Query

When the split goes wrong

• Everything landed in one column. The delimiter you ticked is not the one in the data. Look closely — a fixed-width report may use several spaces rather than a tab.
• Extra empty columns appeared. Consecutive delimiters, such as a double space, are each treated as a separator. Tick "Treat consecutive delimiters as one" on the wizard's second screen.
• Numbers turned into dates. Text to Columns applies a General format that converts anything date-like. On the wizard's third screen, set those columns to Text before finishing.
• Leading zeros disappeared. Same cause. Set that column to Text on the third screen to preserve them.

After splitting, two things commonly go wrong: lookups that cannot find a match because of stray whitespace, and error codes in the formulas that reference the new columns.

Going the other way? How to combine two columns in Excel covers TEXTJOIN and the ampersand, plus why dates turn into numbers when you join them.

Frequently asked questions

How do I split a cell in Excel?

Insert blank columns to the right first, then select the column and choose Data → Text to Columns → Delimited, tick the separator, and click Finish. For irregular patterns, type the first result and press Ctrl + E for Flash Fill.

How do I separate first and last names?

Text to Columns with Space as the delimiter handles most cases. For live formulas, use =LEFT(A2, FIND(" ",A2)-1) for the first name and =TEXTAFTER(A2," ",-1) for the last.

Why does my last-name formula include the middle name?

Because FIND locates the first space, so RIGHT returns everything after it. Use =TEXTAFTER(A2," ",-1), or in older versions =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100)).

Why did Text to Columns delete my data?

It writes its output into the columns to the right without warning. Always insert enough blank columns first — one fewer than the number of pieces you expect.

How do I split a cell into multiple rows?

Text to Columns cannot do it. Use Power Query: Data → From Table/Range, then Split Column → By Delimiter, and under Advanced options choose Rows.

Why did my split turn numbers into dates?

The wizard applies a General format, which converts anything resembling a date and strips leading zeros. On the third screen of the wizard, set the affected columns to Text before clicking Finish.

What is the difference between Flash Fill and Text to Columns?

Text to Columns splits on a delimiter and is predictable. Flash Fill infers a pattern from your example and handles irregular data, but it guesses and produces static values rather than formulas.

Conclusion

Insert the blank columns before you start — that single step prevents the only genuinely destructive outcome here.

Then match the method to the job: Text to Columns for a clean separator, Flash Fill for irregular patterns, formulas when the result must stay live, and Power Query when you will do this again next month.

Related: removing spaces in Excel covers cleaning text before splitting, and Excel dates not recognised covers the date conversion trap.