Split Text in Excel: Into Columns by Space, Comma or Dash

"Sam Lee" into first and last name. "London, UK" into city and country. "INV-2026-0042" into its parts. Excel has a button, a shortcut and formulas for this. Choose based on whether the result needs to update when the data changes.

1. Text to Columns (one-off)

1. Select the column.
2. Data > Text to Columns.
3. Choose Delimited, then the separator (space, comma, other: -).
4. Pick a destination so you don't overwrite neighbouring columns. Finish.

It's static: change the original and the split doesn't update.

2. Flash Fill (pattern-based)

Type the first result yourself (e.g. "Sam" next to "Sam Lee"), then press Ctrl + E. Excel guesses the pattern for the rest. Check a few rows; it can guess wrong on unusual names.

3. TEXTSPLIT (Microsoft 365)

=TEXTSPLIT(A2," ")

Spills each part into its own column. Split on several separators:

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

Also available: TEXTBEFORE(A2," ") and TEXTAFTER(A2," "). Google Sheets has =SPLIT(A2," ").

4. Classic formulas (every version)

First name:

=LEFT(A2,FIND(" ",A2)-1)

Last name:

=RIGHT(A2,LEN(A2)-FIND(" ",A2))

Middle part of "INV-2026-0042":

=MID(A2,FIND("-",A2)+1,FIND("-",A2,FIND("-",A2)+1)-FIND("-",A2)-1)

Long, which is why TEXTSPLIT exists. See LEFT, RIGHT and MID.

Watch out for

• Double spaces: create empty columns. Clean with TRIM first.
• Names with three parts ("Mary Ann Lee"): decide where the split goes.
• Leading zeros: Text to Columns turns "00042" into 42. Set that column's format to Text in step 3.

Frequently asked questions

How do I split a cell into two columns in Excel?

Data > Text to Columns with a delimiter, or =TEXTSPLIT(A2," ") in Microsoft 365.

How do I split first and last name?

=LEFT(A2,FIND(" ",A2)-1) and =RIGHT(A2,LEN(A2)-FIND(" ",A2)).

How do I split text in Google Sheets?

=SPLIT(A2," ") or Data > Split text to columns.

Can I split by line break?

Yes: =TEXTSPLIT(A2,CHAR(10)), or Text to Columns with Ctrl+J as the "Other" delimiter.