Ever received a list where full names, addresses, or data are all crammed into one column? It’s frustrating, especially when you need to sort, filter, or analyze that data. Fortunately, Google Sheets offers easy ways to split text into separate columns—whether you want a quick fix or full control using formulas like SPLIT.
This guide will walk you through multiple ways to split text into columns in Google Sheets, including built-in tools and the SPLIT function. It’s perfect for beginners who want clean, organized data with just a few clicks.
Why Split Text into Columns?
When data is imported or copied from other sources—like websites, emails, or CSV files—it often shows up jumbled in a single column. Splitting text helps you:
- Separate names into first and last names
- Split addresses into street, city, and ZIP code
- Extract email domains or codes from larger strings
- Make data easier to sort and filter
There are two primary ways to split text in Google Sheets:
- Using the built-in “Split text to columns” feature
- Using the
SPLIT()function for more control
Method 1: Use the Built-in “Split text to columns” Tool
This method is the fastest and easiest—ideal for one-time data cleanup.
Steps:
- Select the column or cells with the text you want to split.
- Go to Data → Split text to columns.
- A small dropdown will appear next to your selection. Choose the delimiter (Comma, Space, Semicolon, or Custom).
- The text will automatically separate into adjacent columns.
Real-Life Example: Splitting Full Names
Imagine you have a list of full names in one column:
| Full Name |
|---|
| John Smith |
| Sara Kim |
| David Johnson |
After using “Split text to columns” with a space delimiter, the data becomes:
| First Name | Last Name |
|---|---|
| John | Smith |
| Sara | Kim |
| David | Johnson |
This simple cleanup makes the data easier to filter, group, or mail merge.
Method 2: Use the SPLIT() Function
If you want more flexibility—like handling inconsistent spacing or keeping the original column intact—use the SPLIT() function.
Syntax:
=SPLIT(text, delimiter)
Example: =SPLIT(A2, " ") will split the contents of cell A2 wherever there’s a space.
Advanced Example: Emails and Domains
Let’s say you have email addresses and you want to split the username from the domain:
| alex@example.com |
| sara@health.org |
| john@company.net |
Use this formula in a new column:
=SPLIT(A2, "@")
Output:
| Username | Domain |
|---|---|
| alex | example.com |
| sara | health.org |
| john | company.net |
Other Delimiters You Can Use:
","(comma)";"(semicolon)"|"(pipe character)" "(space)"-"or"/"for dates or phone numbers
Bonus: Split by Line Breaks or Multiple Characters
Want to split text that uses line breaks or other special characters? You can use:
=SPLIT(A2, CHAR(10))
This is useful when copying from sources like emails or PDFs where entries are separated by a new line.
Key Differences Between the Two Methods
| Feature | “Split text to columns” | SPLIT() Function |
|---|---|---|
| One-time or auto-updating | One-time | Dynamic (updates with source cell) |
| Overwrites adjacent data | Yes | No (you choose where to place it) |
| Handles advanced delimiters | No | Yes |
| Best for quick cleanup | ✅ | ➖ |
| Best for formulas or automation | ➖ | ✅ |
Quick Cheat Sheet: How to Split Text in Google Sheets
- Quick split: Select column → Data → Split text to columns
- Formula method: Use
=SPLIT(A2, " ")for more control - Split emails:
=SPLIT(A2, "@") - Split using line breaks:
=SPLIT(A2, CHAR(10)) - Keep your original data intact: Always use the
SPLIT()function in a new column
Splitting text into columns in Google Sheets is a game-changer for cleaning up messy data. Whether you’re handling names, emails, or imported CSV files, you now know two powerful ways to organize it quickly and accurately. Start with the built-in tool for fast fixes, and turn to the SPLIT() function when you need more flexibility.
Try it out in your next spreadsheet and see just how clean and manageable your data can become!