Split text to columns in Google Sheets - Step-by-Step Guide

Introduction

Splitting text to columns helps you separate data in one cell into multiple cells. This is useful when you have combined information like full names or addresses and want to organize them into separate columns.

When you have a list of full names in one column and want to separate first and last names.

When addresses are in one cell and you want to split street, city, and zip code into different columns.

When you receive data with values separated by commas or spaces and want each value in its own column.

When you want to clean up imported data that is all in one column but should be multiple columns.

When you want to prepare data for sorting or filtering by individual parts.

Steps

Step 1: Select

- the column or cells containing the text you want to split

The selected cells are highlighted and ready for splitting

Step 2: Click

- Data menu at the top of the screen

A dropdown menu appears with data options

Step 3: Select

- "Split text to columns" option in the Data menu

A separator menu appears below the selected cells

Step 4: Click

- Separator dropdown menu that appears below the selected cells

A list of separator options is shown

Step 5: Choose

- the separator that matches your data (Comma, Space, Semicolon, Period, or Custom)

The text in the selected cells splits into columns based on the chosen separator

Step 6: If needed,

- choose Custom separator and type the character that separates your text

The text splits correctly using your custom separator

Before vs After

Before

Column A contains full names like "John Smith", "Jane Doe", all in one cell each

After

Column A shows first names "John", "Jane" and Column B shows last names "Smith", "Doe"

Settings Reference

Separator

📍 Dropdown menu below selected cells after choosing Split text to columns

Defines the character used to split the text into columns

Default: Detect automatically

Custom Separator

📍 Custom option in the Separator dropdown menu

Allows splitting text by a specific character not listed in the default options

Default: None

Common Mistakes

Choosing the wrong separator that does not match the text

The text will not split correctly and may stay in one column or split incorrectly

Check the text carefully and select the separator that matches the character dividing your data

Not selecting the cells before using Split text to columns

The tool will not know which cells to split and nothing will happen

Always select the cells or column with the text you want to split first

Summary

Split text to columns separates combined text into multiple columns based on a chosen separator.

Use the Data menu and select Split text to columns, then pick the correct separator.

Make sure to select the correct cells and separator to get the expected split.

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