How to split text in Excel and Google Sheets

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 of Tester. Computers, however, take everything you tell them literally, so if an app has First Name and Last Name fields and you give it Bob Tester, it won’t know what to do (or maybe it will put Bob Tester into the first field and give you an error because the last name field is empty).

If you work with data that needs to be split up like this—like separating first and last names, or separating an address that’s in one column into 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 Microsoft Excel

We’ll use names for our example, to keep things simple. If the names are already in a spreadsheet, open the spreadsheet in Excel—or, if the list is somewhere else, like in a document or on a website, make a new spreadsheet in Excel, then copy and paste the text into the spreadsheet.

1. Select the column with the text you want to split.

2. Click the Data tab in the top menu, and click Text to Columns—you should see it around the middle of the toolbar. That will open the split text dialog.

3. The Delimited option should be checked by default—this specifies that the text you want to separate uses spaces, tabs, or other delimiters. Click Next.

4. Now, choose what’s separating your text. For our list of names, we’ll check the box beside Space. If you have a list of comma-separated values, check the Comma option instead. (Check Other if your character is not listed, then type that character in the box. Say, for instance, you want the domain names from a list of email addresses. You’d check Other, type an @ symbol in the box, and Excel could then split the username and domain from email addresses.)

5. Excel will then show you a preview of how the finished data will look, and will ask you if the split text is plain text or a date—and if the latter, you can set the correct date format.

6. Excel might tell you to select where you want the new columns, in which case, just click on each designated column in your sheet.

7. Click Finish, and you’re done. Your data will be split into the columns you want.

Using Apple Numbers instead? Numbers doesn’t have a built-in tool to split text, but on a Mac, you can use this Automator script to split text. Or, if you’re using Microsoft Word, you can convert text to columns—select the text, then click the Insert tab, then click Table > Convert. You can then separate text by spaces, commas, tabs, special characters, and even paragraphs.

How to split text in Google Sheets

There are even fewer steps to split text in one cell into multiple cells in Google Sheets.

1. Import your spreadsheet into Google Sheets or make a new spreadsheet and paste in the data you want to split.

2. Select the text or column, then click the Data menu and select Split text to columns…

3. Google Sheets will open a small menu beside your text where you can select to split by comma, space, semicolon, period, or custom character.

4. Select the delimiter your text uses, and Google Sheets will automatically split your text.

Want to automate this process so the data is pulled from one app, split, and then sent to another? Use Zapier’s Split Text function to automatically split text and avoid manual data cleanup. Learn more about separating first and last names from fields in your Zaps—the automated workflows you create with Zapier.

This article was originally published in September 2017 and has since had contributions from Elena Alston.