Split Text into Columns in Google Sheets or Excel

The Text-to-Column feature is a powerful tool in both Excel and Google Sheets, allowing you to split data from a single column into multiple columns based on a specific separator, such as a space, comma, or custom delimiter.

This is particularly useful when dealing with names, addresses, or product details that need to be separated into distinct fields.

Using Text-to-Columns in Excel

You can split text into columns in most spreadsheet software. Here’s how to do it in Microsoft Excel.

Splitting Names into First and Last Names

1. Copy the original column – It’s always a good idea to keep the original data intact.
2. Select the column that contains the names.
3. Navigate to Data > Text to Columns.
4. A wizard will appear. Choose Delimited and click Next.
5. Select the delimiter (e.g., Space to split first and last names).
6. Click Finish, and Excel will split the data into separate columns.

Now, the first name and last name are in their respective columns.

Splitting Address Components

What if you want to get specific address data from within a spreadsheet. This is a common request, especially if you’re populating a spreadsheet from Google Forms. Users sometimes enter address data all as one line item, which sometimes gets stuck into a cell all at once.

In those cases, you’ll want to separate the street address, the city, and the state into separate columns.

No problem. Here’s how to do it:

1. Copy and paste the column to preserve the original data.
2. Select the column containing the addresses.
3. Go to Data > Text to Columns.
4. Choose Delimited and click Next.
5. Select “Comma” as the delimiter if addresses are formatted like:
   123 Main St, Los Angeles, CA
6. Click Finish, and Excel will split the street, city, and state into separate columns.

Using Fixed Width to Separate Data

There’s also an easy way, at least in Excel, to use a fixed width to separate data within a cell into multiple columns. Note that this is strictly for data sets where all data has a similar width delimiter.

If the data is structured consistently, you can manually define where to split:

1. Select the column to split.
2. Go to Data > Text to Columns.
3. Choose Fixed Width instead of Delimited.
4. Click within the preview to set breakpoints where the data should split.
5. Click Finish to apply.

However, Fixed Width is not ideal when text varies in length.

Using Split Text-to-Columns in Google Sheets

You don’t need a formula to split text into columns in Google Sheets, either. This feature is pretty simple to use. There’s a built-in Split Text-to-Columns feature.

Steps to Split Data in Google Sheets

1. Select the column containing the data you want to split.
2. Go to Data > Split text to columns.
3. A small dropdown will appear. Choose a separator (e.g., comma, space, semicolon, or period).
4. If the separator isn’t listed, select Custom Separator and manually enter a delimiter (e.g., hyphen (-)).
5. Google Sheets will automatically separate the data into separate cells in new columns.

Note that you can use the split function to accomplish the same thing.

Example: Splitting Product Details

For a dataset like Laptop – Dell – $799, follow these steps:

1. Select the column containing product data.
2. Go to Data > Split text to columns.
3. Choose Custom Separator, enter “-”, and press Enter.
4. The product name, manufacturer, and price will now be in separate columns.

Key Differences Between Excel and Google Sheets

Excel provides more customization with a wizard-based approach, while Google Sheets simplifies the process with a quick dropdown selection. Both are powerful resources for data organization, especially with large datasets.

Use the wizards to split text quickly into columns, or pair more powerful functions like ARRAYFUNCTION and SPLIT to get even more bespoke results.

Note that the Spreadsheet Point YouTube channel has video guides on all of these formulas and provides step-by-step walkthroughs for many common questions. That includes splitting text into columns, extracting data, and cleaning spreadsheets.

Use these methods to clean and organize your data. And, as always, please let us know if you have any questions!