How to Split a Google Sheet Cell into Multiple Columns

Need to split a google sheet cell into multiple columns?

Let's walk you through how to do this.

In our example we have a list of full names, and we want to split this into a first and last name.

So "Fred Adams" would get split into "Fred" and "Adams" in separate columns.

Now what a lot of people would do is attempt to manually type each first and last name into individual columns.

But what happens if we have hundreds and thousands of rows? That's not something we want to do, at least not manually.

We're going to show you how to do this with some built in google sheets functionality called "split text to columns."

Data > Split text to columns

What we'll do is select the "Fred Adams" cell, go to "Data", and select "Split text to columns."

Now what's going to pop up is a little separator dropdown. Google sheets will try to automatically detect what the separator is, but sometimes you'll need to manually select it yourself.

In this instance, it's not automatically splitting it, so we'll need to select the correct option.

Click on the dropdown and you'll see the available options.

Now our data for "Fred Adams" is separated by a space, so select "Space." Once you select that, it should automatically, split your cell into two columns.

You can see that Fred Adams is now properly split.

Now let's do the same for the next row for "Demetrius Charles."

Select the cell, data, text to columns, and select the "Space" dropdown.

It correctly split that item.

Separator detected automatically

Now let's try the cells with commas as separators.

Let's select both the "Fred,Adams" and "Demetrius,Charles" columns.

Select "Data >  split text to columns."

What you notice on this one is that it automatically detected the commas and split it properly.

This won't always happen, as it will depend on how your data is setup.

Custom separator

Let's try the last type with the hyphens in between the first and last names. We'll select the "Fred-Adams" and "Demetrius-Charles" columns. Data > split text to columns.

It didn't detect automatically, so we need to select from the dropdown.

However there is no "-" in the dropdown, so we need to select "Custom." Type in the hyphen "-" and you'll see that it split the cells properly.

Now you know how to add a custom separator depending on how your data is organized.

Multiple cells at once

You can apply the split text to columns on multiple cells at once just by selecting all the cells you want to adjust.

That does require the cells having the same separator, otherwise it won't work. So you would need to select all of the cells with comma separators, or space separators, or hyphen separators for it to work properly.

But this allows for bulk splitting of cells.

Video Tutorial

You can find the video tutorial on splitting cells into multiple columns here.