Grab our practice workbook 👉 HERE and follow along:

To split text in Excel can be tricky, especially when dealing with different delimiters like commas, tabs, or colons. The TEXTSPLIT function simplifies this task.

Why Use TEXTSPLIT?

Unlike the “Text to Columns” tool, TEXTSPLIT is dynamic. It updates automatically when your data changes, saving you time and effort.

Common Issues Solved by TEXTSPLIT

  • Mixed delimiters (commas, semi-colons, colons)
  • Varying number of items per row
  • Delimiters with and without spaces

Consider the example below. It features a wide variety of data with different delimiters in each row and empty spaces.

TEXTSPLIT can handle all these issues with ease. By using this function, you can split text into separate cells quickly and accurately.

Combine TEXTSPLIT with other Excel functions for even more powerful data transformations.

Understanding the Excel TEXTSPLIT Function

The Excel TEXTSPLIT function has the following syntax:

=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [pad_with])

The arguments for TEXTSPLIT are:

  • text – The text you want to split. (Required)
  • col_delimiter – Character(s) that separate text into columns. (Required)
  • row_delimiter – Character(s) that separate text into rows. (Optional)
  • ignore_empty – Specify TRUE to create an empty cell for consecutive delimiters. Defaults to FALSE, which means don’t create an empty cell.  (Optional)
  • pad_with – Value to use for padding. Defaults to #N/A. (Optional)

Let’s look at some examples of how to split strings in Excel with the TEXTSPLIT function.

Featured Course

Black Belt Excel Package

What would your life look like if you became an Excel Black Belt? You’ll get the best deal with this (cost savings) package. It will take you from Excel Newbie to Grand Master.
Learn More
Excel Black Belt course cover

TEXTSPLIT Basics

Let’s use TEXTSPLIT to split listed skills into different cells. Here’s how to do it with an example:

Basic Syntax

=TEXTSPLIT(A2, “,”)
  • A2 is the cell with the combined data.
  • “,” is the delimiter.

The Issue

The spaces after the commas stay, causing leading spaces in the split text.

Solutions

We have two ways we can deal with this issue:

  • Combining with the Excel TRIM Function to remove any extra spaces:
=TRIM(TEXTSPLIT(A2, “,”) )
  • Modifying Delimiter:
=TEXTSPLIT(A2, “, ”)

This defines the delimiter as a comma followed by a space.

These methods help you cleanly split your data without unwanted spaces.

Repeating the Formula Across Cells

We fixed the parsing issue for the first cell. But, there are problems when we use the formula in other cells. This is because of different delimiters like colons and semi-colons.

The good news is that TEXTSPLIT can handle multiple delimiters to split text in Excel. Here’s how:

=TEXTSPLIT(A2, { “,”, “:”, “;” } )
  • Separate each delimiter with a comma.
  • Enclose the list in curly braces.

💡 You can add more delimiters to anticipate future variations in your data.

Featured Course

Excel Essentials for the Real World

Learn time-saving hacks to work smarter in Excel. Our members also consistently report a newfound enthusiasm for using Excel after taking our course.
Learn More

Skipping Blanks in TEXTSPLIT

Let’s split a list of names into first and last names.

The problem is the titles “Mr.” and “Ms.” in the data.

Using Titles as Delimiters:

Let’s use the titles themselves as delimiters to separate the text.

=TEXTSPLIT(A2, { " ", "Mr. ", "Ms. " } )

This splits the names but introduces extra spaces.

Ignoring Empty Values:

Add the ignore_empty argument in the TEXTSPLIT function to avoid this issue:

=TEXTSPLIT(A2,{ " ", "Mr. ", "Ms. " }, , TRUE)

This removes the unwanted spaces and gives clean results.

Split Text Into Columns and Rows

Splitting text into multiple rows and columns is easy with Excel’s TEXTSPLIT function.

Example:

We have data in a single cell that we need to split into a name column and a salary column.

Separate Columns and Rows:

=TEXTSPLIT(A2, "=", ", " )
  • The equals sign ‘=’ separates names and salaries into columns.
  • The comma-space ‘, ‘ splits the data into rows.

Featured Course

Master NEW Excel Functions in Office 365 & Office 2021

Ready to supercharge your Excel skills? This course will help you master the new set of functions introduced to Excel for Office 365. You’ll create professional-grade reports in a fraction of the time it used to take you.
Learn More
Excel new functions course cover

TEXTSPLIT, SORT & Stitch Back (TEXTJOIN)

Let’s try something really cool.

We have the list from earlier of application skills in a combined, unsorted manner, and we wish to have them in a combined, sorted manner.

There isn’t a single function solution to produce this result, but we can achieve the goal in three simple steps:

  1. Split the text into separate cells using the TEXTSPLIT
  2. Sort the separated results using the SORT
  3. Recombine the individual elements back into a single cell using the TEXTJOIN

Step 1 – Split the text

We’ll start by using the Excel TEXTSPLIT function to split the skills into cells.

=TEXTSPLIT(A2, “,”)

NOTE:  I’ve filled the formula down to the adjacent rows to apply the formula to the remainder of the list.  I’ll be doing this for the following steps as well.

Because some (not all) of the comma delimiters have spaces following them, we’ll slip in a little TRIM function to remove the extraneous spaces.

Step 2 – Sort the Separated Skills

We can easily sort the skills by nesting the TRIM/TEXTSPLIT functions inside a SORT function.

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

In the SORT function, we had to skip the 2nd and 3rd arguments ([sort_index] and [sort_order]) to be able to define the 4th argument, [by_col].  The TRUE tells the SORT function to sort the data by columns as opposed to by rows which is the default behavior.

Step 3 – Combine the Results into a Single Cell

The final step is to use a TEXTJOIN function to combine the separate cells into a single cell.

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

Unlocking the Power of TEXTSPLIT

The TEXTJOIN function uses a comma-space as the delimiter, and “TRUE” tells it to ignore empty cells. Since there were no empty cells, “TRUE” or “FALSE” gives the same result.

TEXTSPLIT is a versatile function that solves complex problems easily. Use it creatively with other functions to enhance its power.

No more writing complicated formulas for simple tasks. TEXTSPLIT makes data management easier than ever.

Download the Workbook

Enhance your learning experience by downloading our workbook. Practice the techniques discussed in real-time and master the Excel TEXTSPLIT function with hands-on examples. Download the workbook here and start applying what you’ve learned directly in Excel.

Excel Download Practice file

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.