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.

Practice

1. What does the SPLIT function do in Google Sheets?

easy

A. It breaks text into separate cells based on a chosen separator.

B. It combines multiple cells into one cell.

C. It changes the font style of the text.

D. It sorts data alphabetically.

Solution

1. Step 1: Understand the purpose of SPLIT

   The SPLIT function is designed to divide text into multiple parts based on a separator.

2. Step 2: Compare options with SPLIT's function

   Only It breaks text into separate cells based on a chosen separator. correctly describes this behavior; others describe different functions.

3. Final Answer:

   It breaks text into separate cells based on a chosen separator. -> Option A

4. Quick Check:

   SPLIT = breaks text by separator [OK]

Hint: Remember: SPLIT separates text into columns by delimiter [OK]

Common Mistakes:

• Confusing SPLIT with CONCATENATE or JOIN
• Thinking SPLIT changes text formatting
• Assuming SPLIT sorts data

2. Which of the following is the correct syntax to split text in cell A1 by commas?

easy

A. =SPLIT(A1, ",")

B. =SPLIT(A1; ",")

C. =SPLIT(A1, ',')

D. =SPLIT(A1, ";")

Solution

1. Step 1: Identify correct separator and syntax

   The separator for commas is "," and arguments are separated by commas in Google Sheets formulas.

2. Step 2: Check each option's syntax

   =SPLIT(A1, ",") uses correct comma separator and argument separator. =SPLIT(A1; ",") uses semicolon which is invalid in Google Sheets. =SPLIT(A1, ',') uses single quotes which are not accepted for string literals in formulas. =SPLIT(A1, ";") uses wrong separator ";".

3. Final Answer:

   =SPLIT(A1, ",") -> Option A

4. Quick Check:

   Correct syntax uses commas and double quotes [OK]

Hint: Use commas to separate arguments and double quotes for text [OK]

Common Mistakes:

• Using semicolons instead of commas between arguments
• Using single quotes instead of double quotes for strings
• Using wrong separator character

3. Given cell A1 contains "John,Doe,35,New York", what will =SPLIT(A1, ",") output?

medium

A. John,Doe,35,New York (unchanged)

B. John Doe 35 New York (all in one cell)

C. John | Doe | 35 | New York (each in separate cells horizontally)

D. Error: Invalid separator

Solution

1. Step 1: Understand SPLIT behavior on comma-separated text

   SPLIT divides text at each comma and places each part into adjacent cells horizontally.

2. Step 2: Apply SPLIT to given text

   Text "John,Doe,35,New York" splits into "John", "Doe", "35", and "New York" in separate cells.

3. Final Answer:

   John | Doe | 35 | New York (each in separate cells horizontally) -> Option C

4. Quick Check:

   SPLIT divides text by commas into columns [OK]

Hint: SPLIT breaks text into columns at each separator [OK]

Common Mistakes:

• Expecting all text in one cell after SPLIT
• Thinking SPLIT returns an error for commas
• Confusing SPLIT with CONCATENATE

4. You used =SPLIT(A1, ",") but all text remains in one cell. What is the likely problem?

medium

A. Google Sheets does not support SPLIT.

B. The SPLIT function is misspelled.

C. The formula is missing an equal sign.

D. The separator in the text is not a comma.

Solution

1. Step 1: Check if separator matches text content

   If the text in A1 does not contain commas, SPLIT with comma separator won't split anything.

2. Step 2: Verify other options

   SPLIT is spelled correctly, formula has equal sign, and Google Sheets supports SPLIT, so these are not issues.

3. Final Answer:

   The separator in the text is not a comma. -> Option D

4. Quick Check:

   Separator must match text content for SPLIT to work [OK]

Hint: Match separator exactly to text characters [OK]

Common Mistakes:

• Using wrong separator character
• Forgetting equal sign in formula
• Assuming SPLIT is unsupported

5. You have a list of full names in column A like "Jane Smith". How do you split first and last names into columns B and C using SPLIT?

hard

A. =SPLIT(A1, ",")

B. =SPLIT(A1, " ")

C. =SPLIT(A1, "-")

D. =SPLIT(A1, ";")

Solution

1. Step 1: Identify the separator in full names

   Full names like "Jane Smith" are separated by a space character.

2. Step 2: Use SPLIT with space separator

   Using =SPLIT(A1, " ") splits the name into first and last names into adjacent columns.

3. Final Answer:

   =SPLIT(A1, " ") -> Option B

4. Quick Check:

   Split full names by space to separate first and last [OK]

Hint: Use space " " as separator for splitting names [OK]

Common Mistakes:

• Using comma or other wrong separators
• Not using quotes around separator
• Trying to split without specifying separator