Text & Cleanup

Excel formula for Split text into separate columns

Split a cell's text on a delimiter into multiple columns with TEXTSPLIT.

Copy this formula

=TEXTSPLIT(A2, " ")

How the formula works

TEXTSPLIT (Excel 365) breaks text at each delimiter and spills the parts across columns. For older Excel, extract the first part with LEFT + FIND.

=TEXTSPLIT("John Smith", " ")→John | Smith

Splits a full name into first and last across two cells.

=TEXTSPLIT("red,green,blue", ",")→red | green | blue

Splits on commas into three cells.

=TEXTBEFORE("[email protected]", "@")→anna

Returns only the part before the delimiter.

First word (legacy)

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

Returns the text before the first space.

TEXTSPLIT isn't available — what now?

Use Data ▸ Text to Columns for a one-off split, or LEFT/MID/RIGHT with FIND for a formula in older Excel.

How do I split text into rows instead of columns?

Put the delimiter in the third argument: =TEXTSPLIT(A2, , ",").

How do I split on more than one delimiter?

Pass them as an array: =TEXTSPLIT(A2, {",",";"}).

How do I get just the last word?

Use =TEXTAFTER(A2, " ", -1) in Excel 365. A negative instance number counts from the end.

Need a formula for your own data?

Describe what you want in plain language and let ExcelGPT write, explain, and verify the formula — free to start, no plugin.