8 Ways to Use the TEXTSPLIT Function in Excel

Still splitting text with Text to Columns, one file at a time?

TEXTSPLIT does it in a formula — and unlike Text to Columns, it updates when
your data changes. In this video I’ll show you 8 ways to use it: across columns,
down rows, with multiple delimiters, handling gaps, and controlling case.

Don’t have Excel 365? I cover alternative methods that work in older versions too.

Watch: 8 TEXTSPLIT techniques in 11 minutes

Prefer reading? The same steps are written out below the video.

Step-by-step

TEXTSPLIT (Excel 365) splits text wherever a character you choose, the delimiter, appears. Unlike Text to Columns, the result updates when the source changes. Its arguments are TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode]).

1. Split into columns

A2 holds a comma-separated list of fruits. In C2:

=TEXTSPLIT(A2,",")

Each fruit spills into its own column.

2. Split into rows

Leave the column delimiter empty and give the row delimiter instead:

=TEXTSPLIT(A2,,",")

The two commas skip the second argument, so the list runs down the rows.

3. Split on a comma and a space

When names are separated by “, “, include the space in the delimiter so there are no leading spaces:

=TEXTSPLIT(A2,", ")

4. Split into columns and rows at once

For text like Name=Ann, Age=34, City=Paris, use one delimiter for columns and another for rows:

=TEXTSPLIT(A2,"=",", ")

The equals sign splits across, and the comma-space splits down, giving a neat grid.

5. Use several delimiters

If the text mixes commas and semicolons, put them in curly braces, and wrap TRIM around the result to remove stray spaces:

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

6. Keep empty values

By default TEXTSPLIT doesn’t ignore empty values, so two delimiters in a row create an empty cell. That keeps columns lined up when some scores are missing:

=TEXTSPLIT(A2,", ")

7. Ignore upper and lower case

To split on both ” x ” and ” X “, set the fifth argument (match mode) to 1:

=TEXTSPLIT(A2," x ",,,1)

Including the spaces around the x stops leading and trailing spaces in the results.

8. Split a date into day, month and year

Dates are stored as numbers, so convert them to text first, then split on the slash:

=TEXTSPLIT(TEXT(A2,"dd/mm/yyyy"),"/")

No Excel 365?

Use Data > Text to Columns, or combine LEFT, RIGHT, MID and SEARCH or FIND to pull out each piece.

Go further

My Excel Tips & Shortcuts: 100+ Powerful Hacks course collects a hundred time-saving techniques like this one.