SPLIT function in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Output of SPLIT with multiple delimiters

What is the output of the formula =SPLIT("apple,banana;cherry|date", ",;|") in Google Sheets?

Google Sheets

=SPLIT("apple,banana;cherry|date", ",;|")

A ["apple", "banana", "cherry", "date"]

B ["apple,banana;cherry|date"]

C ["apple", "banana;cherry|date"]

D ["apple", "banana", "cherry|date"]

💡 Hint

Remember SPLIT can use multiple characters as delimiters if listed in the second argument.

The SPLIT function splits the text at any character found in the delimiter string. Here, comma, semicolon, and pipe are delimiters, so the text splits into four parts.

📊 Formula Result

intermediate

SPLIT with empty delimiter

What happens when you use =SPLIT("hello", "") in Google Sheets?

Google Sheets

=SPLIT("hello", "")

A Error: SPLIT delimiter cannot be empty

B ["hello"]

C ["h e l l o"]

D ["h", "e", "l", "l", "o"]

💡 Hint

Think about whether SPLIT accepts an empty string as a delimiter.

SPLIT requires a non-empty delimiter. Using an empty string as delimiter causes an error.

❓ Function Choice

advanced

Choosing the right function to split text into columns

You have a cell with text "John|Doe|35|Engineer". Which formula correctly splits this text into separate columns by the pipe character?

A =TEXTSPLIT(A1, "|")

B =SPLIT(A1, "|")

C =SPLIT(A1, " ")

D =SPLIT(A1, ",")

💡 Hint

Check which delimiter matches the text separator.

The pipe character "|" is the delimiter in the text. SPLIT with "|" correctly separates the text into columns.

🎯 Scenario

advanced

Splitting text but keeping some delimiters

You want to split the text "red,green;blue|yellow" into separate cells by comma, semicolon, or pipe, but keep the semicolon as part of the output. Which formula achieves this?

A =SPLIT("red,green;blue|yellow", ",|") with SUBSTITUTE to replace semicolon

B =SPLIT("red,green;blue|yellow", ",;|")

C =SPLIT(SUBSTITUTE("red,green;blue|yellow", ";", "[semi]"), ",|")

D =SPLIT("red,green;blue|yellow", ",|")

💡 Hint

Try replacing the semicolon with a unique string before splitting.

Replacing semicolon with a unique string prevents SPLIT from splitting at semicolons, so semicolons stay in output.

❓ data_analysis

expert

Counting items after SPLIT

Given cell A1 contains "cat,dog,bird,fish", which formula correctly counts how many items result from =SPLIT(A1, ",")?

A =COUNT(SPLIT(A1, ","))

B =LEN(SPLIT(A1, ","))

C =COUNTIF(SPLIT(A1, ","), "*")

D =COUNTA(SPLIT(A1, ","))

💡 Hint

Think about which function counts non-empty text cells.

COUNTA counts non-empty cells, so it correctly counts the number of items after SPLIT. COUNT counts numbers only, LEN returns length of first item, COUNTIF with "*" counts non-empty but is less direct.