Split Text into Columns

Excel Formulas › Text

Excel 365TEXTSPLITLegacy alt

Need to break Widget-West-2026 into separate cells? In Excel 365 the TEXTSPLIT function does it in one formula that spills across columns; in older versions you combine LEFT/MID/FIND or use Text to Columns.

Quick formula: to split the text in A2 on every hyphen:
=TEXTSPLIT(A2, "-")
The result spills into adjacent cells — one piece per column. Use a row delimiter as the third argument to also split down.

Functions used (tap for the full reference guide):

TEXTSPLITTEXTBEFORETEXTAFTER

The example

Codes that pack three fields into one cell, split on the hyphen.

ABCD
1CodePart 1Part 2Part 3
2Widget-West-2026WidgetWest2026
3Gadget-East-2025GadgetEast2025

The formula

Entered once in B2, the pieces spill across B:D:

=TEXTSPLIT(A2, "-") // → Widget | West | 2026

How it works

TEXTSPLIT cuts the string wherever it finds the delimiter:

  1. The first argument is the text to split (A2).
  2. The second is the column delimiter — "-" here. Each piece between hyphens becomes its own cell.
  3. The result spills to the right automatically; you enter the formula in one cell only.
  4. Pass an array of delimiters like {"-"," "} to split on several characters at once.

Try it: interactive demo

Live demo

Enter text and a delimiter; see TEXTSPLIT’s pieces.

Variations

Just the first or last piece

TEXTBEFORE / TEXTAFTER grab one side without spilling:

=TEXTBEFORE(A2, "-") → Widget =TEXTAFTER(A2, "-", -1) → 2026 (last piece)

Legacy: first piece with LEFT + FIND

Any version — everything up to the first hyphen:

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

No formula: Text to Columns

Select the column, Data → Text to Columns → Delimited → choose the delimiter. Best for a one-time split.

Pitfalls & errors

TEXTSPLIT needs Excel 365. Excel 2021 and older show #NAME?. Use TEXTBEFORE/TEXTAFTER (also 365), LEFT/MID/FIND, or Text to Columns.

#SPILL! The pieces need empty cells to land in. Clear the cells to the right of the formula.

Missing delimiters create blanks. Set the fourth argument ignore_empty to TRUE to skip empty results from consecutive delimiters: =TEXTSPLIT(A2,"-",,TRUE).

Practice workbook

📊
Download the free Split Text into Columns practice workbook
Coded strings with the TEXTSPLIT result, the TEXTBEFORE/AFTER and LEFT/FIND alternatives, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I split text into columns with a formula?
In Excel 365 use =TEXTSPLIT(A2, "-"), which spills each piece into its own column. In older versions combine LEFT/MID/FIND or use Data > Text to Columns.
How do I get just the part before or after a character?
Use =TEXTBEFORE(A2, "-") for the part before the first hyphen and =TEXTAFTER(A2, "-", -1) for the part after the last one.
Does TEXTSPLIT work in Excel 2021?
No. TEXTSPLIT is Excel 365 only. Excel 2021 and earlier return #NAME?. Use LEFT/MID/FIND formulas or Text to Columns there.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Extract first & last name · Clean extra spaces · Join text with a delimiter

Function references: TEXTSPLIT · TEXTBEFORE · TEXTAFTER