How to Split Text in Excel Using Formulas

To split text in Excel using formulas, choose a function that matches your Excel version and the separator in your data.

A simple comma-delimited example will show how the results differ, including what to do when spacing or missing values cause trouble.

Split text with TEXTSPLIT in Microsoft 365 and Excel 2024

TEXTSPLIT is the most direct option in Microsoft 365 and Excel 2024.

It separates text by a delimiter and spills the results into neighboring cells.

Suppose cell A2 contains North,East,West.

Enter this formula in B2:

=TEXTSPLIT(A2,",")

The formula returns North in B2, East in C2, and West in D2.

The comma is the column delimiter, so the pieces spread horizontally.

To return each piece in a separate row instead, use the row delimiter argument:

=TEXTSPLIT(A2,,",")

In this formula, the second argument is intentionally blank.

The third argument tells Excel to split at commas and place the values vertically, starting in B2.

For text separated by both commas and semicolons, provide an array of delimiters:

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

For example, North,East;West splits into three values.

In some regional settings, Excel uses semicolons between formula arguments and a different array-constant separator.

If Excel rejects the formula, adapt punctuation to your locale.

Handle spaces and empty pieces

Delimited values often have spaces after punctuation.

If A2 contains North, East, West, splitting on a comma leaves leading spaces in the second and third results.

Set ignore_empty to TRUE and trim each result with TRIM:

=TRIM(TEXTSPLIT(A2,",",,TRUE))

This spills cleaned values across columns.

The fourth argument, ignore_empty, skips empty pieces created by repeated delimiters.

If you need to preserve empty fields because their position carries meaning, set that argument to FALSE or omit it.

For example, splitting North,,West on a comma normally gives an empty middle value.

Skipping it changes the position of West, so only ignore empty pieces when removing blanks is appropriate for your data.

Split text at a fixed character or word

If each value has one predictable boundary, use TEXTBEFORE and TEXTAFTER instead of splitting every piece.

With Lee, Jordan in A2, these formulas separate the last name and first name:

=TEXTBEFORE(A2,",")
=TRIM(TEXTAFTER(A2,","))

The first formula returns Lee; the second returns Jordan.

These functions are available in Microsoft 365 and Excel 2024.

They are useful for extracting a prefix or suffix, such as a username before an at sign or a file extension after a period.

To split at a space instead, replace the comma delimiter with " ".

Be careful with names and addresses: a space may occur multiple times, and the first or last space may not represent the boundary you intend.

Use formulas in older Excel versions

Excel 2019, Excel 2016, and some earlier releases do not include TEXTSPLIT.

For a value such as North,East,West in A2, extract the first item with:

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

To extract the second item, use:

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

To extract the final item after the last comma, use:

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

These formulas rely on FIND, LEFT, MID, RIGHT, SUBSTITUTE, and REPT, which are available in older Excel versions.

The final-item formula replaces each comma with a long run of spaces, then reads the last segment.

Increase 100 if a segment may exceed that length.

Older-version formulas are less convenient when the number of pieces varies.

Copying an extraction formula across columns may require adjusting the formula for each delimiter position.

For frequent or complex splits, Power Query's Split Column command can be easier to maintain. Its results are produced by a query and can be updated by refreshing the query.

Check delimiters and missing matches

A delimiter must match the text exactly.

A comma, semicolon, hyphen, line break, and ordinary space are different characters.

If a formula returns an error or does not split as expected, inspect the source cell for inconsistent punctuation, nonbreaking spaces, or extra characters copied from another system.

In older Excel, FIND returns an error when the delimiter is absent.

You can make the first-item formula return the original text when no comma exists:

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

For a dynamic-array formula, results need empty cells into which they can spill.

If B2:D2 is occupied, TEXTSPLIT may return a #SPILL! error.

Clear the blocking cells or move the formula to an open area.

A formula entered inside an Excel Table may also behave differently from a spill formula outside the table.

Choose the right formula for your data

• Use TEXTSPLIT when you want to separate a value into multiple rows or columns and have Microsoft 365 or Excel 2024.
• Use TEXTBEFORE and TEXTAFTER when you need only the text before or after one known delimiter in supported versions.
• Use combinations of LEFT, MID, RIGHT, and FIND when a workbook must work in older Excel releases.
• Use TRIM to remove ordinary extra spaces around extracted values, while checking separately for nonbreaking spaces.

Before filling a formula down a column, test it on records with no delimiter, repeated delimiters, and spaces around the separator.

Those examples reveal whether your formula should preserve blank pieces, return the whole source value, or report that the input needs cleanup.