Excel: Split String Based on Multiple Delimiters

---

You can use the TEXTSPLIT function in Excel to split a string into multiple columns based on a specific delimiter.

To use the TEXTSPLIT function with multiple delimiters, you can use the following syntax:

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

This particular example will split the text in cell A2 using a space, underscore, comma or semi-colon as a delimiter.

The following example shows how to use this formula in practice.

Example: How to Split String Based on Multiple Delimiters in Excel

Suppose we have the following column of names in Excel:

Notice that the first and last names are split by a variety of different delimiters, including:

• Spaces ( )
• Underscores ( _ )
• Commas ( , )
• Semi-colons ( ; )

We can type the following formula into cell B2 to split the text in cell A2 based on any of these four delimiters:

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

We can then click and drag this formula down to each remaining cell in column B:

The formula splits the names in column A into two new columns by splitting the text based on either a space, an underscore, a comma or a semi-colon.

How This Formula Works

Recall the formula that we used to split the text based on multiple delimiters:

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

Here is how this formula works:

The first argument of the TEXTSPLIT function specifies the cell that contains the text to split.

The second argument of the TEXTSPLIT function uses curly brackets to create an array { } that contains the delimiters that should be used for splitting the text.

Notice that each of the delimiters are separated by commas.

The end result is that we’re able to split each of the names in column A into two new columns based on any of the delimiters in the array.