You’re human. So when you look at the text Bob Tester, you quickly recognize that an individual with the first name Bob has the last name Tester. Computers, however, take everything you tell them literally. So if an app has a Full name field and you enter Bob Tester, it won’t know which part is the first name and which is the last.
If you work with data that needs to be split—like separating first and last names, or splitting an address that’s in one column into separate street address, city, state, and ZIP Code columns—spreadsheets will take care of it for you. Here’s how to split text in Excel and Google Sheets.
How to split text in Excel
To keep this demo simple, we’ll use full names, but the same steps apply no matter what type of data you’re splitting.
To follow along, copy and paste the following list of names into an empty Excel spreadsheet (or you can use an existing list of names):
-
Bob Tester
-
Joe Smith
-
Jane Doe
-
Madonna
-
Sir Elton John
-
Sandra Day O’Connor
Before we get started, make sure you have a few empty columns to the right of your text. This way, Excel will have empty cells to populate your split text. Otherwise, it might end up overwriting your other data.
-
Select the column or cell range with the text you want to split.
-
In the Excel ribbon, click Data.
-
Click Text to Columns.
-
In the Text to Columns pop-up, select your delimiters (what’s separating your data). In this example, the delimiter is Space because there’s a space between each word. Excel will also show you a preview of how the split text will look.
If you have a list of comma-separated values, such as
123 Main Street, Toronto, Ontario, click the delimiter Comma. And if you’re using a delimiter that’s not listed, enter your character in the Custom Value box. Say, for instance, you want the domain names from a list of email addresses; you’d enter@as your custom value. From there, Excel would split the username and domain into separate cells. -
Click Apply.
That’s it.
Before you hit save and call it a day, review your newly split text. If your original data don’t all follow the same format, like one first name and one last name, Excel will automatically separate any extra data into extra columns. For example, Excel will split the name Sir Elton John across three columns: Sir, Elton, and John. This means you may have to do some extra data cleanup.
How to split text in Google Sheets
The process of splitting text in Google Sheets is similar to Excel. As with Excel, make sure you have a few empty columns to the right of your text before you separate your data in Google Sheets.
-
Select the column or cell range with the text you want to split.
-
Click Data, and then select Split text to columns.
By default, Google Sheets automatically tries to guess how you want to split your data and separates the text accordingly. In my experience, though, this only works if your data is split by a character like a period, comma, or semicolon—not a space.
-
In the Separator pop-up that appears next to your selected text, click the dropdown.
-
Click your delimiter: Comma, Semicolon, Period, Space, or Custom. For this example, click Space.
You’re all set.
Automate data organization
Let’s say you’ve launched a marketing campaign. The leads are rolling in, but there’s a problem: the contact names are in one line. You need them to be split into first and last names before uploading your lead data to your CRM and email marketing tool.
With Zapier, you can automate everything from formatting your text to sending your lead data to your marketing platforms. Learn more about how to automatically split and format text, or get started with one of these workflows.
Related reading:
This article was originally published in September 2017 and has since had contributions from Elena Alston and Jessica Lau. The most recent update was in June 2024.
