Table of Contents
The SPLIT function in Google Sheets allows users to separate text by using multiple delimiters, such as commas, spaces, or symbols. This function is particularly useful for organizing data into different columns or cells based on specific criteria. By specifying the delimiters within the function, users can easily split text into smaller segments and manipulate the data as needed. This feature is beneficial for tasks such as categorizing information, creating lists, or analyzing data sets. Overall, the SPLIT function in Google Sheets provides a convenient and efficient way to separate text using multiple delimiters, enhancing the organization and manipulation of data.
Google Sheets: 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:
- 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.
The following tutorials explain how to perform other common tasks in Google Sheets:
Cite this article
stats writer (2024). How can I use the SPLIT function in Google Sheets to separate text using multiple delimiters?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-the-split-function-in-google-sheets-to-separate-text-using-multiple-delimiters/
stats writer. "How can I use the SPLIT function in Google Sheets to separate text using multiple delimiters?." PSYCHOLOGICAL SCALES, 23 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-the-split-function-in-google-sheets-to-separate-text-using-multiple-delimiters/.
stats writer. "How can I use the SPLIT function in Google Sheets to separate text using multiple delimiters?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-the-split-function-in-google-sheets-to-separate-text-using-multiple-delimiters/.
stats writer (2024) 'How can I use the SPLIT function in Google Sheets to separate text using multiple delimiters?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-the-split-function-in-google-sheets-to-separate-text-using-multiple-delimiters/.
[1] stats writer, "How can I use the SPLIT function in Google Sheets to separate text using multiple delimiters?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I use the SPLIT function in Google Sheets to separate text using multiple delimiters?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.