Whether you’re working with data imported from external sources or a dataset you created, some columns or cells might contain information that needs to be separated into different cells.

For example, you might have an address contained in a single cell. Each address will contain multiple pieces of information separated by a comma, and you need to split these out into separate columns.

There are many ways you can split cell data into multiple columns. In this post, you will discover three easy ways to split cells into multiple columns in Google Sheets.

• Split cells using split text to columns,
• Split cells using smart fill.
• Split cells using the SPLIT function.

Download the example workbook to follow along.

Separate Cell Values Using Split Text to Columns

The Split Text to Columns feature in Google Sheets works great for separating your data based on delimiters.

Follow these steps to use the Split Text to columns feature.

1. Select the column containing the data you want to split.

2. Go to the Menu bar and click on the Data tab. Do not unselect the data you want to split.

3. From the drop-down menu, select the Split text to columns option.

4. Once you click on the Split text to columns option, your data will be separated into new columns. A Separator dialogue box will appear below your dataset showing that Google Sheets has automatically identified the separator in your dataset using the Detect automatically feature.

You can click on the Detect automatically option to reveal other separator options. To clear out the dialogue box, simply press the Esc key on your laptop.

Detect automatically works perfectly on datasets that are separated with commonly used delimiters such as spaces, commas, and periods.

It might not work properly if you want to split text characters that have unusual separators as found in URLs and Email addresses.

For this, you should use the Custom option.

For example, if you want to split an email follow these steps.

1. Follow the previous steps 1 to 4 mentioned above.

2. You will notice that Google Sheet’s didn’t split the text into new columns after clicking on the Split text to columns option as earlier. This is because it cannot automatically detect the delimiter in the text values. You will have to tell the spreadsheet what it should use as a separator.

To do this, in the Separator dialogue box, click on Detect automatically to reveal the other separator options.

3. Click on the Custom option.

4. Type the @ symbol into the text box that appears and the Email addresses will be split into new columns.

These steps also work if you want to split other text strings with nonconventional separators.

When using the Split text to columns feature, there are a few things you should note.

• Split text to columns overrides the data in the selected column and replaces it with a part of the text that has been split.
• Split text to columns can only use one separator at a time to split text values. It doesn’t support the use of multiple separators to split text. So, if your text string is separated by multiple delimiters, you might have to use other splitting techniques.
• It requires that you understand separators and how to use them. Separators are simply characters around which you want to split your data. When a separator is specified, the spreadsheet will look for it, and anywhere it finds it, it will initiate the split.

As you will soon find out, understanding separators and how they work isn’t only restricted to the Split text to columns feature.

Split Cells Using Smart Fill

The SMART FILL feature is a powerful tool that Google recently introduced to the spreadsheet.

It works by identifying patterns in data and providing suggestions that you can use to automate the data entry process.

Smart fill can do a host of other things that will make your data manipulation work much faster such as merging first and last names into a single cell.

However, we will focus on how you can use it to split cells into columns.

From this sample data set containing a list of people’s names and their location, you can extract the names using Smart fill.

1. Type the text you want to into the adjacent column.

2. Repeat Step 1. This time, you will notice that immediately you begin to type the next name, a SMART FILL dialogue box pops up. In the box, you will see Formula suggestions you can use to complete the process.

The spreadsheet can make this suggestion because SMART FILL has identified a pattern in your entry. In addition to suggesting a formula, it also shows you the range of cells to which the formula will be applied.

3. If you want to see the suggested formula, click on the Show formula option in the dialogue box.

4. To accept the suggested formula, click on the checkmark (✔) in the dialogue box.

You have successfully split the names from the location. These steps can be used to split the remainder of the text values into new columns.

If you notice the formula bar, you will see that it contains the formula used to generate the split text values. This solution makes the results dynamic and flexible for adding new data.

If you later add new entries to the bottom of your dataset, you can copy and paste the formula down the rows as needed.

Unlike the Split text to columns feature, SMART FILL doesn’t override entries in the first column.

New records can be easily appended to the bottom of your dataset, and you can split them filling down the formulas created by the SMART FILL feature.

Drag down or double-click the fill handle at the bottom right-hand corner of the active cell to copy down the formula.

Now that the cell reference is on cell B1, the formula bar shows this particular cell isn’t affected by the formula used to generate the remainder of the split text.

This is because the suggested formula began on the cell where SMART FILL discovered the pattern. If you want to apply the suggested formula, you simply reference a cell that has the formula and drag the fill handle up.

A better solution will be to use column headers. When you work with tables that have headers, SMART FILL applies the suggested formula to the whole column.

Table headers give SMART FILL more functionality. They allow SMART FILL to do something quite ingenious.

Unlike in the earlier example, this dataset has column headers.

Now, you can split the names from the location following the same steps from the previous example.

You see that SMART FILL applies the formula to the whole range because of the table headers.

Notice what formula pops out when the Show formula option is selected.

SMART FILL has used the VLOOKUP function to generate the result! This means SMART FILL works across sheets. It can do this because it uses functions to generate results based on the table headers.

There are limitations to using SMART FILL to divide the text into columns.

• It doesn’t work for splitting numbers. So, if your cells contain text and numeric values and you want to separate the text from the numeric values, Smart fill will not be able to help you do that. You might have to apply other text splitting methods such as using Text to Columns or the SPLIT function.
• Smart fill identifies data patterns and uses them to predict the values to split. However, it’s not capable of identifying complex patterns. You might want to use the Smart fill feature for simple text splitting actions.

Check out this post to discover everything you can do with Smart Fill in Google Sheets.

Using the SPLIT Function

The SPLIT function is the last method for splitting cell data to consider.

Like other functions, the SPLIT function has a specific purpose, which is to divide text values into separate cells.

Using SPLIT is the most flexible way to separate text strings. Once you have a mastery of the syntax, then you can apply it to split anything you want with ease.

To appreciate SPLIT, you must understand that it allows you to divide text strings using more than one delimiter.

If your text data is separated by a comma, a colon, or a period, for instance, SPLIT will allow you to separate the text using each of these delimiters.

Syntax for the SPLIT Function

SPLIT ( text, delimiter, [split_by_each], [remove_empty_text] )

The SPLIT function takes four arguments, but only the first two are required: The text and delimiter arguments. The last two are optional. Nevertheless, it is important to explore how to apply all arguments in the function.

Here’s how each argument works.

• text refers to the text that you want to split.
• delimiter is the separator character around which you want to split the text. It’s similar to the separator in the Split text to columns feature. You should know that the separators are enclosed in double-quotes and separated by commas when you’re using more than one.
• split_by_each is the first optional argument that takes only TRUE or FALSE. By default, the argument is set to TRUE, that is when you have more than one separator in the delimiter argument, SPLIT will separate the text values around each of the delimiters.
  • When set to FALSE, SPLIT considers all the separators as a single value and divides the text accordingly.
  • For a better understanding of the split_by_each argument, think of it as asking this question; Should SPLIT consider the delimiters as individual values? If yes, you type in TRUE. If not, you type in FALSE.
• remove_empty_cells is the last and also the second optional argument. This argument tells SPLIT what to do with empty cells in the output. It also takes TRUE or FALSE values.
  • TRUE will remove empty cells from the results and FALSE will keep them.
  • Blank cells can occur as the result of your split for a few reasons. By default, or when omitted, this argument is set to TRUE.

Like the split_by_each argument, you can understand remove_empty_cells by asking this question; Should SPLIT remove empty cells from the output?

If yes, type in TRUE. If no, type in FALSE.

There are a few things you should note before beginning to use the SPLIT function.

• Delimiters are case sensitive. To SPLIT, the character a is different from A.
• You should create enough empty cells to the right when using the SPLIT function. If you want to split your text into 2 columns, you should leave at least 3 columns free space to accommodate for any spillover.

Example with the SPLIT Function

The Formula Text column shows how the SPLIT function has been used to produce the results in the corresponding columns C, D, E, and F.

If you look closely, you will notice that SPLIT has been used with several variations of the optional parameters and the results have been affected accordingly.

The text in cell A2 is split using only one delimiter. Split_by_each and remove_blank_cell arguments are omitted.

In rows 3 and 4, the text is separated based on three delimiters.

These delimiters or separators are passed into the delimiter argument. What differs between the SPLIT syntax in rows 3 and 4 is the split_by_each section.

When set to TRUE in row 3, the text is split around the separators. But when set to FALSE, the result returns the text unseparated. Since there are no instances where the delimiters appear together, the command returns the original text values.

Take a look at the text string cell A5, see that the delimiters have now been put together, and the split_by_each argument is set to TRUE. As expected, the result is the same as what is returned in row 3.

In rows 6 and 7, you can see how the remove_empty_cells argument affects the results.

The text to be split has two commas after Almelo. When the remove_empty_cells argument is set to TRUE, the output comes out normally. But when it’s set to FALSE in row 7, a blank row is created after the cell containing the Almelo text.

Why this happens is because when splitting cells, the separator around which the text is divided is removed.

In this case, the separator is a comma. But there are two commas after Almelo in the original text, with no values between them.

The SPLIT function removes this empty part, retains the space after it creates the blank cell, then deletes the next comma and continues splitting the remaining text. All these happened because remove_empty_cells is set to TRUE.

Conclusion

In Google Sheets, there is usually no shortage of methods to achieve the same result.

Google Sheets offers 3 easy ways to separate your data into multiple columns.

Text to columns is a quick option for those one-time use scenarios.

For splitting more complex data the smart fill feature might suit your needs. It also generates formulas for you that can be reused when you get more data.

The SPLIT function is the perfect dynamic way to separate data based on one or more delimiters.

Which method do you prefer? Do you know any others? Let me know in the comments below!