SPLIT function in Google Sheets - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the SPLIT function do in Google Sheets?

The SPLIT function divides text into separate parts based on a chosen separator, placing each part into its own cell.

beginner

Syntax of SPLIT function?

SPLIT(text, delimiter, [split_by_each], [remove_empty_text])

- text: The text to split.
- delimiter: The character(s) to split by.
- split_by_each (optional): TRUE splits by each character in delimiter, FALSE treats delimiter as whole.
- remove_empty_text (optional): TRUE removes empty cells from results.

beginner

What happens if you use SPLIT("apple,banana,orange", ",")?

The text "apple,banana,orange" is split at each comma, resulting in three cells: "apple" | "banana" | "orange".

intermediate

How does the optional parameter split_by_each affect SPLIT?

If split_by_each is TRUE (default), SPLIT breaks text at each character in the delimiter string.
If FALSE, SPLIT looks for the whole delimiter string to split by.

intermediate

What does the remove_empty_text parameter do in SPLIT?

If remove_empty_text is TRUE (default), empty cells created by consecutive delimiters are removed.
If FALSE, empty cells appear where delimiters are next to each other.

What is the correct way to split the text "red|green|blue" into separate cells using SPLIT?

A SPLIT("red|green|blue", "|")

B SPLIT("red|green|blue", ",")

C SPLIT("red|green|blue", " ")

D SPLIT("red|green|blue")

The delimiter is the pipe character "|", so you must specify it as the second argument.

If you use SPLIT("a--b--c", "--", FALSE), what will happen?

A Text does not split at all.

B Text splits at each '-' character.

C Text splits at the whole "--" string.

D An error occurs.

With split_by_each set to FALSE, SPLIT looks for the whole delimiter "--" to split the text.

What does SPLIT("one,,three", ",", TRUE, FALSE) return?

A one | , | three

B one | three

C one,,three

D one | (empty) | three

remove_empty_text is FALSE, so empty cells from consecutive commas are kept.

Which parameter controls whether SPLIT treats the delimiter as a whole or as separate characters?

A split_by_each

B delimiter

C text

D remove_empty_text

The split_by_each parameter controls how the delimiter is interpreted.

What is the default value of remove_empty_text in SPLIT?

A No default

B TRUE

C Depends on delimiter

D FALSE

By default, SPLIT removes empty text cells created by consecutive delimiters.

Explain how to use the SPLIT function to separate a list of names separated by semicolons into individual cells.

Think about what character separates the names and how SPLIT uses that.

Describe the effect of the split_by_each and remove_empty_text parameters in the SPLIT function.

Consider how splitting changes if delimiter is treated as whole or by each character, and what happens with consecutive delimiters.

Practice

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

easy

A. It finds the largest number in a list.

B. It breaks text into parts based on a separator.

C. It changes text to uppercase.

D. It adds numbers together.

Solution

1. Step 1: Understand the purpose of SPLIT

   The SPLIT function is designed to separate text into smaller pieces using a character or characters as a divider.

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

   Only It breaks text into parts based on a separator. describes breaking text into parts, which matches SPLIT's behavior.

3. Final Answer:

   It breaks text into parts based on a separator. -> Option B

4. Quick Check:

   SPLIT breaks text = It breaks text into parts based on a separator. [OK]

Hint: Remember: SPLIT cuts text by chosen characters [OK]

Common Mistakes:

• Confusing SPLIT with math functions
• Thinking SPLIT changes text case
• Assuming SPLIT finds max or min values

2. Which of these is the correct syntax to split the 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 SPLIT function requires the text and the separator as arguments, separated by commas. The separator here is a comma ",".

2. Step 2: Check each option's syntax

   =SPLIT(A1, ",") uses commas correctly and the right separator. =SPLIT(A1; ",") uses semicolon which is invalid in Google Sheets formulas. =SPLIT(A1, ";") uses wrong separator ";". =SPLIT(A1) misses the separator argument.

3. Final Answer:

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

4. Quick Check:

   Correct syntax with comma separator = =SPLIT(A1, ",") [OK]

Hint: Use commas to separate arguments, specify separator in quotes [OK]

Common Mistakes:

• Using semicolons instead of commas
• Forgetting to put separator in quotes
• Omitting the separator argument

3. Given cell A1 contains "apple,banana,cherry", what will =SPLIT(A1, ",") return?

medium

A. ["apple,banana,cherry"] as one cell

B. Error because separator is missing

C. ["apple"; "banana"; "cherry"] in separate cells vertically

D. ["apple", "banana", "cherry"] in separate cells horizontally

Solution

1. Step 1: Understand SPLIT behavior with comma separator

   SPLIT breaks text at each comma and places each piece in adjacent cells horizontally by default.

2. Step 2: Analyze the output for given input

   Input text "apple,banana,cherry" splits into "apple", "banana", and "cherry" placed side by side in cells.

3. Final Answer:

   ["apple", "banana", "cherry"] in separate cells horizontally -> Option D

4. Quick Check:

   Split by comma = horizontal cells with parts [OK]

Hint: SPLIT places parts horizontally by default [OK]

Common Mistakes:

• Expecting vertical split without specifying
• Thinking output stays in one cell
• Confusing separator presence

4. The formula =SPLIT(A1, ",", "TRUE") gives an error. What is the likely cause?

medium

A. The third argument TRUE is invalid for SPLIT.

B. The separator must be a number, not a string.

C. The separator argument is missing.

D. SPLIT does not accept more than two arguments.

Solution

1. Step 1: Review SPLIT function arguments

   SPLIT accepts up to four arguments: text, delimiter, split_by_each (TRUE/FALSE), and remove_empty_text (TRUE/FALSE). The third argument is split_by_each and must be a boolean.

2. Step 2: Check if TRUE is valid here

   The third argument is "TRUE" (a string), but SPLIT expects a boolean TRUE/FALSE (without quotes) for split_by_each. A string causes a type mismatch error.

3. Final Answer:

   The third argument TRUE is invalid for SPLIT. -> Option A

4. Quick Check:

   Check argument types carefully [OK]

Hint: Check argument count and types carefully [OK]

Common Mistakes:

• Assuming SPLIT only takes two arguments
• Confusing separator type requirements
• Ignoring optional arguments

5. You have a cell A1 with text "John;Doe;30;New York". How do you split this text into separate cells vertically (one below another) using SPLIT?

hard

A. =TRANSPOSE(SPLIT(A1, ";"))

B. =SPLIT(A1, ";", TRUE)

C. =SPLIT(A1, ";", FALSE, TRUE)

D. =SPLIT(A1, ";")

Solution

1. Step 1: Understand SPLIT default behavior

   SPLIT splits text into cells horizontally by default, so the parts appear side by side.

2. Step 2: Use TRANSPOSE to convert horizontal output to vertical

   Wrapping SPLIT with TRANSPOSE changes the horizontal array into a vertical one, placing each part in cells below each other.

3. Final Answer:

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

4. Quick Check:

   Use TRANSPOSE to switch horizontal to vertical [OK]

Hint: Wrap SPLIT with TRANSPOSE for vertical output [OK]

Common Mistakes:

• Expecting SPLIT alone to split vertically
• Misusing optional arguments for vertical split
• Forgetting to use TRANSPOSE for vertical layout