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.