How to Split Cells in Google Sheets

This easy-to-follow guide will show you how to split cells in Google Sheets.

Google Sheets enables you to sort, format, and organize data in a number of different ways, including by splitting one cell into multiple cells.

There are many obvious use cases for splitting a cell, such as decoupling first and last names into distinct columns, splicing an address into different fields, or separating numbers and text into their own cells.

The following guide will teach you how to split cells in Google Sheets, based on step-by-step tutorials and real life examples.

Video Walkthrough: How to Split Cells in Google Sheets

Why Split Cells in Google Sheets?

Google Sheets allows you to split cells into separate columns to divide data more efficiently.

When you split a cell in Google Sheets, you can help make your data more accessible and readable.

Here are some other benefits of splitting cells in Google Sheets:

• Enhanced data organization. Splitting cells is especially useful when filtering large datasets. This makes data easier to read and analyze.

• Improved data accuracy. Avoid data entry errors by splitting cells instead of entering data manually.

• Increased time-savings. Instead of manually separating data, divide data into individual cells quickly.

• More flexibility. Splitting cells provides more flexibility when performing calculations, creating visualizations, and producing reports.

With all of these different benefits, splitting cells empowers you to speed up your data analysis and insights in Google Sheets.

4 Methods for Splitting Cells in Google Sheets

1. Split text to columns option

The fastest way to split cells in Google Sheets is to use the Split text to columns option in the Data menu.

To demonstrate an example, let’s import a sample Zendesk dataset into Google Sheets using Coefficient.

Read our blog on how to connect Zendesk to Google Sheets for a full walkthrough of the process.

The Coefficient app allows you to import live data into Google Sheets. Connect your business systems, such as Zendesk, HubSpot, and Salesforce, and pull business data into Sheets instantly.

Now, on to the example.

Let’s split the names in our sample dataset under column F. This will splice the first and last names into individual cells.

First, create a new column next to column F.  Select the range of cells containing the names (in this case, F3:F18).

Next, click on the Data menu and select Split text to columns from the dropdown list.

Choose a separator to split the text, or let Google Sheets detect one automatically. You can also use a custom separator to split your cells.

Once you choose your separator, you will see the cell values (first and last names) split into separate cells.

Keep in mind: you can also use the Split text to columns option if your cell contains more than two text strings.

2. Google Sheets SPLIT function

The SPLIT function in Google Sheets segments data based on a specified string or character. Then each segment is placed in individual cells.

The number of resulting cells depends on how many segments you define.

Before using the SPLIT function, let’s go over its syntax to understand how to build the formula.

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

• text is the text you wish to split.

• delimiter refers to the character (or characters) used to split the text parameter.  For example, if your delimiter is “and”, your text gets divided around the “a”, “n”, and “d” characters.

• split_by_each is an optional argument that you can set to TRUE or FALSE. This instructs the formula whether to split text around each character within the delimiter argument.

• remove_empty_text is another optional argument you can set to TRUE or FALSE to remove empty text segments.

Let’s try a SPLIT formula on the same sample dataset we used in the first example, specifically cell C3.

Type the formula below into cell D3.

=SPLIT(C3,” “)

C3 is the text parameter. The space enclosed in double quotes is the delimiter.

The text will segment every time a space (“ “) is encountered. The segments are outputted across the row, starting at cell D3.

Therefore, in this example, each word in the text parameter is outputted across the row.

Now drag down your formula across column D. The resulting formulas will split the text contained in column C into individual words.

Remember to have enough blank cells in the row to accommodate the split text, or you’ll receive an error message.

For instance, cell D5 below shows an error because the existing cells are not enough to accommodate the split text.

The SPLIT function offers a quick and more flexible way to divide cells in your spreadsheet.

However, consider the following factors when using the SPLIT function to ensure seamless data aggregation, organization, formatting, and manipulation.

• Output. The SPLIT function outputs the split text as an array. Consider using other functions, such as TRANSPOSE or JOIN, to format the output correctly.

• Text format. The SPLIT function works best with text in a consistent format. If the text you want to split is inconsistent, use other functions, such as FIND or SUBSTITUTE, to clean it up before using a SPLIT formula.

• Error handling. The SPLIT function returns an error if your specified delimiter is not in the text. You can use the IFERROR function to handle these errors.

Now let’s take a look at how you can combine the SPLIT function with other Google Sheets functions.

3. SPLIT function + ARRAYFORMULA function

Another way to split cells is by combining the SPLIT and ARRAYFORMULA functions. The syntax looks as follows:

=arrayformula(split(data, delimiter, [split_by_each], [remove_empty_text]))

Suppose you want to split the date and time in column G, and then place the results in a new sheet within the same spreadsheet.

This is where the SPLIT and ARRAYFORMULA combo comes in. Type in the following formula:

=arrayformula(split(‘Zendesk Tickets’!G3:G18,” “))

Zendesk Tickets is the sheet that will receive the split cells. G3:G18 is the range of cells with the text you want to split (in this case, date and time).

Your split date and time will look like this on the spreadsheet:

4. SPLIT function + QUERY function

You can use the Google Sheets QUERY function with a SPLIT formula to splice cells and exclude unnecessary data.

Let’s say you want to split the Assignee Name column below, but you need to exclude the extra texts, such as No ID and C8.

If you used a SPLIT formula by itself, this would produce results with the unwanted text.

To resolve this, use a QUERY function with your SPLIT formula, in a fashion similar to this:

=query(arrayformula(split(‘Zendesk Tickets’!E3:E18, ” “)),”select Col1, Col2”)

The combined SPLIT and QUERY formula divides first and last name into separate cells, minus the text you don’t want.

Leverage AI to Build Your Split Cell Formulas

You can use Coefficient’s free Formula Builder to automatically create the Google Sheets formulas to split cells, create pivots, charts, and more. You’ll never search for another Google Sheets tutorial again.

First, you’ll need to install the free Google Sheets Extension. You can get started with Formula Builder here.

After you submit your email, follow along, and accept the prompts to install. Once the installation is finished, navigate to Extensions on the Google Sheets menu. Coefficient will be available as an add-on.

Now launch the app. Coefficient will run on the sidebar of your Google Sheet. Select GPT Copilot on the Coefficient sidebar.

Then click Formula Builder.

Type a description of a formula into the text box. For this example, let’s split the full name in column A to create first names and last names in Columns B and C. Simply type: Split first name last name in column A2:21 on Sheet8.

Then press ‘Build’. Formula Builder will automatically generate your split cell formula.

And, it’s that easy! Simply, place the formula in the desired cell.

Common Mistakes When Splitting Cells in Google Sheets

Common mistakes can lead to errors when you split cells in Google Sheets. Here’s what some of those mistakes are, and how you can avoid them:

• Forgetting to create a backup or copy of your original data. Before splitting cells, make a copy of the original data so you can go back to it if you make a mistake. Not doing this step can cause you to lose important data since you can’t unsplit the cells after splitting.

• Choosing an incorrect delimiter. Selecting the wrong delimiter can cause your data to not split correctly, so choose a delimiter based on your specific use case to avoid potential mishaps.

• Not having the correct number of columns or rows. When splitting cells, you must maintain the correct number of columns or rows. If you select too many or too few, your data will not be split correctly.

• Not formatting your split data properly. Format your data correctly after splitting cells. For example, change dates, redo text alignment, or adjust the column widths to avoid formatting errors.

You should be mindful of these common mistakes to avoid errors and unlock the full power of cell splitting in Google Sheets.

Conclusion

Learning even the basics of Google Sheets formulas can go a long way, regardless what career path you’re on. Leveraging AI, however, can ensure you learn and advance your skillset quicker than you ever expected.

Splitting Cells in Google Sheets: Enhance Data Organization & Analysis

Splitting cells in Google Sheets helps you organize and analyze your data more efficiently in your spreadsheet.

You can split cells in Google Sheets to improve data accuracy, save time, increase flexibility, and supercharge shareability.

Splitting cells help eliminate manual work, just like Coefficient.

Coefficient offers AI formula, pivot, and chart generation, and the ability to automatically import data from your business systems into Google Sheets, so you don’t have to spend hours manually copying-and-pasting data into your spreadsheet.