Split text to columns in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Split a full name into first and last name

You have the full name "John Doe" in cell A1. Which formula correctly splits the first and last name into separate cells?

A =TEXTSPLIT(A1, ",")

B =SPLIT(A1, " ")

C =SPLIT(A1, ",")

D =TEXTSPLIT(A1, " ")

💡 Hint

Think about what character separates the first and last name.

The SPLIT function splits text by a specified delimiter. Since the names are separated by a space, =SPLIT(A1, " ") correctly splits the full name into first and last names.

Options A and B use TEXTSPLIT, which is not available in Google Sheets. Option B splits by comma, which does not match the space delimiter.

❓ Function Choice

intermediate

Choose the correct function to split CSV data

You have a cell A1 containing the text "apple,banana,cherry". Which function will split this text into separate cells for each fruit?

A SPLIT(A1, " ")

B TEXTSPLIT(A1, ",")

C SPLIT(A1, ",")

D TEXTJOIN(A1, ",")

💡 Hint

Look for the function that divides text by commas.

SPLIT is the correct function in Google Sheets to divide text by a delimiter. Since the fruits are separated by commas, SPLIT(A1, ",") splits the text into separate cells.

TEXTSPLIT is not supported in Google Sheets. TEXTJOIN combines text, so option C is incorrect. Option C splits by space, which does not match the comma delimiter.

🎯 Scenario

advanced

Split a date-time string into date and time

Cell A1 contains the text "2024-06-15 14:30:00". You want to split this into date and time in two separate cells. Which formula will do this correctly?

A =TEXTSPLIT(A1, " ")

B =SPLIT(A1, ":")

C =SPLIT(A1, "-")

D =SPLIT(A1, " ")

💡 Hint

What separates the date and time in the string?

The date and time parts are separated by a space. Using =SPLIT(A1, " ") splits the string into date and time correctly.

Option D splits by colon, which splits the time but not the date-time correctly. Option D uses TEXTSPLIT, which is not supported in Google Sheets. Option D splits by dash, which splits the date but not the time.

📊 Formula Result

advanced

Result of splitting a text with multiple delimiters

Cell A1 contains "red;green,blue|yellow". You want to split this text into separate cells by any of the delimiters ;, ,, or |. Which formula produces the correct split?

A =SPLIT(A1, ";,|", TRUE, TRUE)

B =SPLIT(A1, ";,|", FALSE, TRUE)

C =SPLIT(A1, ";,|", TRUE)

D =SPLIT(A1, ";,|")

💡 Hint

Check the parameters for splitting by multiple delimiters.

In Google Sheets, SPLIT can split by multiple delimiters if the third parameter split_by_each is TRUE and the fourth parameter remove_empty_text is TRUE.

Option A correctly uses =SPLIT(A1, ";,|", TRUE, TRUE) to split by any of the delimiters and remove empty cells.

Option A treats the entire string ";,|" as one delimiter, so it won't split correctly. Options C and D have incorrect parameter combinations.

❓ data_analysis

expert

Count how many columns result from splitting text

Cell A1 contains "one,two,three,four". You use the formula =COUNTA(SPLIT(A1, ",")). What is the result?

A 4

B 0

C 1

D Error

💡 Hint

How many items are separated by commas in the text?

The SPLIT function splits the text into 4 parts: "one", "two", "three", and "four".

COUNTA counts how many non-empty cells are in the result, which is 4.

So the formula returns 4.