How to Split Cells in Google Sheets

Picture this. Your boss provides a share link to a Google Sheets file filled with a list of company details. He then tells you to split each full address into a separate column.

While you could do it manually, it would take a long time to finish and can be quite tedious, especially if you have a sheet with a large dataset.

A more efficient approach is to use built-in functions in Google Sheets that can split your address or any other text in a matter of seconds.

How to Split Cells in Google Sheets?

Before you begin, you should know that these methods work, given that you have at least one character that’s recurring across the cells you want to split.

Apart from it, there are mainly two ways to split a text in Google Sheets, one of which can even split your data vertically across multiple rows.

Using Split Columns to Text Option

Google Spreadsheet has a built-in tool called “Split Columns to text option,” which can split your cell horizontally. It uses a delimiter to split the cells and provides common delimiters like commas, semicolons, periods, and spaces.

In addition to it, you can also input a custom one that matches appropriately your text.

You can use it as follows.

  1. Select the cell whose contents you want to split. To split multiple cells, hold and drag the cursor over the desired cells.
    How to Split Cells in Google Sheets
  2. Then, click the Data menu at the top and select the Split text to columns option.
    How to Split Cells in Google Sheets
  3. On the small Separator prompt, click Detect automatically and select a suitable delimiter.
    How to Split Cells in Google Sheets
  4. Alternatively, if your text contains a delimiter other than the default options, select Custom and enter it there. In this case, the custom delimiter is “–“.
    How to Split Cells in Google Sheets
  5. Your split text output should now appear on the right side of the selected cell.
    How to Split Cells in Google Sheets

Note: This method places the output split data on the right side of the selected cell. Therefore, use it only if the cells on its right are empty. Otherwise, the output data will replace them completely.

Using the Split Function

Unlike the above method, the SPLIT function is more powerful as you can apply the same split function for other cells as well. Also, you can use it in combination with other functions like TRANSPOSE.

Before you use the Split function, we recommend you have a look and understand its syntax to use it in a much better way.

Syntax:

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

Any function, including SPLIT, takes/receives special values called arguments. While the text and delimiter arguments can’t be empty, you can skip the other two arguments. Also, if you don’t mention them inside the SPLIT function, they will act according to their default values.

 What each argument means:

  • =: Indicates the start of a formula or a function.
  • SPLIT: Function used to split the text.
  • Text: Text you want to split. Generally, you enter a cell reference.
  • Delimiter: Common character (s) that’s common/recurring across multiple cells based on which split action is performed.
  • Split_by_each(Optional):  Used when you have multiple delimiters and take a boolean value only. (Either true or false). Also, it is set to TRUE by default and treats each one separately unless you specify otherwise.
  • remove_empty_text(Optional) : Adds space between the split output. Initially, its value is set to TRUE, which means empty space will be added after each split column. 

To use the split function, follow these steps:

  1. Select the destination cell where you want to display the split output.
    How to Split Cells in Google Sheets
  2. Then, type SPLIT, enter the arguments that best suit your case, and select a cell to split. Also, don’t forget to enclose them in parentheses.
    How to Split Cells in Google Sheets
  3. After entering the necessary arguments, press Enter, and the split output will appear next to the selected cell.
    How to Split Cells in Google Sheets
  4. Additionally, you can even apply the same function to other cells. For this, hover the bottom-right corner of the cell on which you performed the split function previously.
    How to Split Cells in Google Sheets
  5. Next, double-click when the cursor changes to a plus icon and the cells below it will automatically populate with their split output next to their corresponding rows.
    How to Split Cells in Google Sheets

Note: If you already have some pre-populated cells next to the selected cell, the split function prevents you from overwriting them and instead gives a #REF! Error.

Furthermore, if you have more than one delimiter in your data, you can use the split_by_each argument as follows.

CASE 1: When split_by_each is set to TRUE.

By default, this argument is set to TRUE, which means each delimiter is treated as a separate entity.

CASE 2: When split_by_each is set to FALSE.

If you specifically set it to FALSE, it will scan text where all the delimiters are placed together consecutively. Otherwise, it returns the exact text without splitting.

To get a clear idea of how it works, let’s look at an example.

How to Split Cells in Google Sheets

Example: Split by Each

Here, we have a phone number with the person’s full name. We want to split the phone number and full name into different columns. Now, we can use the delimiters @ and + to achieve the desired result.

i.e. =SPLIT(=SPLIT(A71,”@+.”,true)

  • A71: cell reference of the text we want to split.
  • “@+” : the two delimiters enclosed inside a double quote.
  • TRUE: split_by_each set to TRUE, which treats @ and + as separate delimiters.

How to Split Cells in Google Sheets

Split by each set to TRUE

However, if we set the split_by_each to FALSE, both delimiters must exist together, and you must enter the exact order in which they occur.

Here’s what happens when you set it to false. ie., =SPLIT(A71,”@+”,false).

How to Split Cells in Google Sheets

Split by each set to FALSE

Use the Third-Party Add-Ons

You can add various add-ons on Google Sheets that help you to split your cells. However, you need to download and install them first.

Here’s how to do it:

  1. Click the Extensions menu and select Add-ons > Get add-ons.
    How to Split Cells in Google Sheets
  2. Search for split or similar and install one that provides the required split functions.

How to Split Cells Vertically?

When you split a cell, it will usually be done across multiple columns in a horizontal manner. However, if you want the split output in the same column and this time across multiple rows, you can again use the split function.

First, you split the cells and then transpose them to convert the rows into columns. When writing the formula, it may seem counter-intuitive that the transpose function is before the split function. But we are performing the split function and then transposing it.

You can split cells vertically as follows:

  1. Select the cell where you want the output to appear.
    How to Split Cells in Google Sheets
  2. Enter the necessary arguments in the SPLIT function as mentioned above.
  3. Next, enclose it with the TRANSPOSE function. For instance, TRANSPOSE(SPLIT()).
    How to Split Cells in Google Sheets
  4. Press the Enter key, and your split output will be displayed vertically on a single column.
    How to Split Cells in Google Sheets

NEWS RELATED

Google Pixel 7a: release date, price, specs, and all other rumors

Google’s Pixel family of smartphones has made a notable impact on the Android smartphone world, but not in the way we saw coming a few years back. Flagship Pixel smartphones are great, but where the Pixel really shines is in the budget space. In 2023, the next phase of the affordable ...

View more: Google Pixel 7a: release date, price, specs, and all other rumors

Waze App Is Now Accessible on Select Cars With Google Built-In — Which Cars Can Do So?

Google built-in drivers now have another alternative GPS driving app to use. Google recently revealed that Waze is launching a new, dedicated app on select cars with Google built-in, allowing the drivers of those vehicles to enjoy all the features Waze has to offer. Google did not disclose when ...

View more: Waze App Is Now Accessible on Select Cars With Google Built-In — Which Cars Can Do So?

Google Photos app is making it easier to search for similar faces

Google Photos has a Lens feature built-in that lets you scan your photo and search for similar results around the web. While this is beneficial if you are desperately looking for similar items your favorite actor has worn, it is not a very attractive feature for normal users. Well, ...

View more: Google Photos app is making it easier to search for similar faces

Google Search Brings Continuous Scrolling to Desktop Users in the US

Google search queries on desktop will now return results in a continuous scroll instead of being divided into several pages, the company announced today (via The Verge). The change is reminiscent of the way modern social media feeds work, although Google’s version isn’t quite the “infinite scroll” that social network ...

View more: Google Search Brings Continuous Scrolling to Desktop Users in the US

Google’s first foldable phone may not perform as well as Galaxy Z Fold 4

Samsung has been selling foldable phones for four years, and Google is expected to launch its first foldable phone in the first half of 2023. However, the upcoming Pixel Fold smartphone may not be able to perform as well as the Galaxy Z Fold 4, and there’s a reason ...

View more: Google’s first foldable phone may not perform as well as Galaxy Z Fold 4

Meta threatens to remove all news from Facebook if bill passes Congress

Facebook’s parent company Meta warned Monday that the social media giant could remove news from its platform if Congress signs off on a proposal that would make it easier for news organizations to bargain collectively with tech giants. In a bid to rescue the lagging local news industry, lawmakers are ...

View more: Meta threatens to remove all news from Facebook if bill passes Congress

How To Backup Google Drive? Best Ways To Keep Your Data Secure

Key Takeaways Google Drive is a cloud storage service that lets you keep a backup of all your data. It is vital to backup Google Drive data as there is a risk of data loss for various reasons. Individual and business users will have access to their backup data even ...

View more: How To Backup Google Drive? Best Ways To Keep Your Data Secure

Update your Google Pixel 7 now for three big security and audio features

Every few months, Google releases Feature Drops with new features and fixes for its Pixel phones. While 2022 may be nearly over, that hasn’t stopped Google from pushing one final Feature Drop before the year ends. Contents The Pixel Watch also gets a couple of new tricks More things still ...

View more: Update your Google Pixel 7 now for three big security and audio features

Google Pixel Watch Will Add Popular Apple Watch Feature

Google Shutters Its Duplex for Web Ticket Booking Tool

December 2022’s Android Security Update is rolling out to Pixels today

December’s Feature Drop brings new features to Pixel devices – rolling out today!

Google Play Store to use a floating bubble as a download progress indicator

Google foldable phone appears on Geekbench with Tensor G2

Google claims that Apple Messages are stuck in the 1990s

Galaxy Watch wearables could soon get Gmail and Google Calendar apps

Google Duplex Will No Longer Be Available on the Web by 2023

Rep. McCarthy: Google, Facebook should be investigated after Twitter report

7 Awesome Google Drive Features You May Not Know About

The 5 best Google Pixel 7 series features that make it worth buying

OTHER NEWS

Breaking thailand news, thai news, thailand news Verified News Story Network