how to, how to

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, how to
  2. Then, click the Data menu at the top and select the Split text to columns option.
    how to, how to
  3. On the small Separator prompt, click Detect automatically and select a suitable delimiter.
    how to, how to
  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, how to
  5. Your split text output should now appear on the right side of the selected cell.
    how to, how to

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, how to
  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, how to
  3. After entering the necessary arguments, press Enter, and the split output will appear next to the selected cell.
    how to, how to
  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, how to
  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, how to

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, how to

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, how to

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, how to

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, how to
  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, how to
  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, how to
  4. Press the Enter key, and your split output will be displayed vertically on a single column.
    how to, how to

TECH NEWS RELATED

New Handy Google Chrome Shortcuts That You Should Know About!

Google has just unveiled new Google Chrome shortcuts. And they can indeed come in handy in different scenarios. Basically, before these shortcuts, there was no easy way to search through history, bookmarks, and tabs. Users would need to take the long and painstaking method of manually searching for web pages. ...

View more: New Handy Google Chrome Shortcuts That You Should Know About!

Infinite scrolling comes to Google Search

Why it matters: It was one of the staples of classic web search, but not any more: Google has just announced the retirement of paged results for the company’s SERP functionality, offering users (almost) infinite scrolling instead. With just a simple tweet posted two days ago, Google has announced one ...

View more: Infinite scrolling comes to Google Search

7 Best PC & Console Games on Android Smartphones

The gaming industry has traditionally concentrated on creating games for PCs and large consoles like the Xbox, and PlayStation. However, as smartphone gaming has become more popular, developers have been forced to create games that can be played on both iOS & Android devices. In fact, more than 72.3% of ...

View more: 7 Best PC & Console Games on Android Smartphones

This free Android app gave my phone cloning superpowers

Today, we look at an Android app that lets you run two instances of the same app completely isolated from each other. Whether it’s to ensure that one Android app doesn’t read data from the other, have two different accounts for messaging or social media apps (like WhatsApp and Instagram), or ...

View more: This free Android app gave my phone cloning superpowers

How to get Google featured snippets: 9 optimization guidelines

Here's a set of guidelines to follow when optimizing for the featured snippet – like using an “is” statement and defining the topic concisely.

View more: How to get Google featured snippets: 9 optimization guidelines

What are Singaporeans looking for on Google Search in 2022?

Singaporeans are curious, practical, and are good at finding ways to have a good time. That is, if you go by what we searched for on Google’s search engine in 2022. Google unveiled its annual Year in Search 2022 lists, which shows trending searches in our area while giving ...

View more: What are Singaporeans looking for on Google Search in 2022?

Jinshin is an upcoming pixel-art JRPG that's coming to Android this month, now open for pre-registration

KEMCO has announced that Jinshin, the studio’s mobile RPG on Android with pixel-art aesthetics, is now open for pre-registration on the Google Play Store. Players can get first dibs on the game as soon as it launches later this month, offering an ancient RPG narrative for fans of old-school ...

View more: Jinshin is an upcoming pixel-art JRPG that's coming to Android this month, now open for pre-registration

Waze launches on Android Automotive…but for Renaults only

Snapshot Built-in Waze is finally arriving for Android Automotive touchscreens in 2023 But only limited to two new Renault models at launch Shares code with Android Auto app, released in 2017 Community-based navigation app Waze will finally launch for Android Automotive in-car systems in 2023, but it’ll only be available ...

View more: Waze launches on Android Automotive…but for Renaults only

Sporting events, CoWIN dominate India's Google searches in 2022

Proton Drive iOS and Android Apps Now Available for Encrypted Cloud Storage

Google’s Year in Search 2022 was dominated by Wordle, Ukraine and a cricket World Cup

Google Pixel 7 Pro Review: Rising to meet the occasion

Browser Slow? How to Make Google Chrome Fast Again

Google Chrome just got handy new shortcuts you need to know

Proton Drive now available to iPhone and Android with encrypted cloud storage

Amazon Makes Shopping Easier With Alexa Shopping List For Android And iPhone

How to Remove Sound from Video on Android

Top 8 Ways to Fix WhatsApp Status Not Loading on Android and iPhone

Google Year in Search 2022: Indians Searched the Most About IPL 2022, Brahmastra, FIFA World Cup

How to use the free VPN on your Google Pixel 7 (and why you should)

OTHER TECH NEWS

Top Car News Car News