Google Sheets: How to Use SPLIT with Multiple Delimiters

The SPLIT function in Google Sheets can be used to divide text based on a specific delimiter.

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

=SPLIT(REGEXREPLACE(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 Use SPLIT Function with Multiple Delimiters in Google Sheets

Suppose we have the following column of names in Google Sheets:

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

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

=SPLIT(REGEXREPLACE(A2, "_|,|;", " "), " ")

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

Google Sheets SPLIT function with multiple delimiters

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.

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

=SPLIT(REGEXREPLACE(A2, "_|,|;", " "), " ")

Here is how this formula works:

First, the REGEXREPLACE function replaces each underscore, comma and semi-colon in cell A2 with a space (the | symbol stands for “OR”).

For example, the name Bob_Erickson becomes Bob Erickson.

Next, the SPLIT function splits the text in column A based on where the space occurs.

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

The following tutorials explain how to perform other common tasks in Google Sheets: