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:
- 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:
=SPLIT(REGEXREPLACE(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:
=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.
Additional Resources
The following tutorials explain how to perform other common tasks in Google Sheets:
Google Sheets: How to Split Text into Rows
Google Sheets: How to Split Date & Time
Google Sheets: Check if Cell Contains Text from List