Split text to columns 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

How do you split the text "apple,banana,orange" into separate cells using a comma as the separator?

Use the formula: =SPLIT("apple,banana,orange", ","). This will put "apple", "banana", and "orange" into separate cells horizontally.

intermediate

Can SPLIT function handle multiple separators at once?

Yes! You can provide multiple separators as a string. For example, =SPLIT(A1, ",; ") splits text at commas, semicolons, or spaces.

beginner

What happens if the separator is not found in the text when using SPLIT?

The entire text stays in the original cell because there is nothing to split.

beginner

How can you split text into columns without using a formula?

Use the menu: Select the cells → Click Data → Choose Split text to columns → Pick the separator from the dropdown.

Which formula splits the text "red|green|blue" into separate cells using the pipe symbol?

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

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

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

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

The pipe symbol | is the separator, so it must be used as the second argument in SPLIT.

What will happen if you use =SPLIT("hello world", ",")?

A Text splits into two cells at the space

B Formula returns an error

C Text splits into two cells at the comma

D Text stays in one cell because comma is not found

Since there is no comma in "hello world", the text stays in one cell.

How do you split text using multiple separators like comma and space?

A =SPLIT(A1, ",")

B =SPLIT(A1, ",; ")

C =SPLIT(A1, ", ")

D )" ," ,1A(TILPS=

You list all separators inside the second argument as a string, like ",; " to split at commas, semicolons, or spaces.

Which menu option lets you split text to columns without typing a formula?

A Data > Split text to columns

B Tools > Split columns

C Format > Split text

D Insert > Split text

The correct menu is Data > Split text to columns.

If you want to split text vertically into rows instead of columns, what should you do?

A Use TRANSPOSE with SPLIT

B Use SPLIT normally

C Use SPLIT with a vertical separator

D Use the menu option Split text to rows

SPLIT splits text into columns by default. To get rows, wrap SPLIT with TRANSPOSE.

Explain how to use the SPLIT function to separate text into columns in Google Sheets.

Think about what character divides your text.

Describe two ways to split text into columns in Google Sheets.

One way uses a formula, the other uses a menu option.

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