How to Split Cells in Google Sheets

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

OTHER NEWS

Spain vs Germany live stream: How to watch World Cup fixture online and on TV tonight

Spain take on Germany with the chance to send the four-time World Cup 2022 winners to the brink of back-to-back group stage exits. Hansi Flick’s side were stunned by a ... Read more »

Is Spain vs Germany on TV tonight? Kick-off time, channel and how to watch World Cup fixture

Germany look to hit back at this World Cup 2022 after a shock defeat to Japan when they take on freescoring Spain. Hansi Flick’s side must beware a second straight ... Read more »

How to Create a Combo Chart in Google Sheets

Create two columns with your primary data series in the left column and the secondary data on the right. Then, click Insert > Chart and insert a Combo Chart. Google ... Read more »

Valheim Mistlands delay to fix bugs “has been worth it”

The Valheim Mistlands delay to focus on fixing bugs in the core game “has been worth it,” lead artist Robin Eyre tells PCGamesN of the Norse indie survival game Read more »

SpaceX launches Dragon spacecraft to send suppliers to ISS

On December 26, Elon Musk’s SpaceX sent the “Falcon 9” rocker carrying the “Dragon” spacecraft to Kenned Space Center in Florida. The goal was to transport suppliers to the International ... Read more »

How to watch Poland vs Argentina on TV & live stream

Lewandowski is off the mark at the World Cup | PressFocus/MB Media/GettyImages Argentina have the chance to go top of Group D with a win over Poland on Wednesday at ... Read more »

NFL Week 12: How to Watch Packers vs. Eagles on NBC, RedZone and More

The NFL's holiday weekend fun continues on Sunday. Read more »

Bucs vs. Browns Livestream: How to Watch NFL Week 12 Online Today

Want to watch the Tampa Bay Buccaneers play the Cleveland Browns? Here's everything you need to stream Sunday's 1 p.m. ET game on Fox. Read more »

Chargers vs. Cardinals Livestream: How to Watch NFL Week 12 Online Today

Want to watch the Los Angeles Chargers take on the Arizona Cardinals? Here's everything you need to stream Sunday's 1:05 p.m. PT game on CBS. Read more »

Auction house to sell off Made.com stock after website goes bust – how to get a bargain

FURNITURE and accessories from the website Made.com will now be sold at auction after the site went bust. Over 30,000 sofas, beds, tables and more will be sold by John ... Read more »

Xiaomi 13 render confirms a flat OLED screen

According to reports, the new Xiaomi 13 series will finally launch on December 1. Xiaomi is yet to disclose the launch date, but the leaks went ahead. The smartphones have ... Read more »

Croatia vs Canada live stream: How to watch World Cup fixture online and on TV

GettyImages-1245012938.jpg Croatia and Canada meet at the Khalifa International Stadium both searching for their first win of this 2022 World Cup in Qatar. The 2018 runners-up Croatia were held to ... Read more »

Realme 10 Pro+ Pricing Tipped In a Meme Posted by VP Madhav Sheth

The Realme 10 Pro and Realme 10 Pro+ were launched in China last week. Both devices are slated to launch globally on December 8 and this should mark the date ... Read more »

The “i” Icon on an Apple Watch: What It Is and How to Use It

DenPhotos/Shutterstock.com The “i” icon on an Apple Watch is used to display information about your watch and to pair it to an iPhone manually. You can also use the “i” ... Read more »

How to counter a Destroy Deck in Marvel Snap

Image via Second Dinner One of the most common decks any Marvel Snap player will encounter focuses entirely on the Destroy ability. This is the first strategic deck that most ... Read more »

Realme 10 Pro+ to cost less than $300 (INR 25,000) in India

Earlier this month, Realme introduced the Realme 10 series in China. The company split the releases throughout the month, with the Realme 10 Pro and Pro+ appearing a few weeks ... Read more »

iPhone SE 4: 3 unaswered questions about the phone

iPhone had a very controversial timeline for the release of the iPhone SE series. The company launched it in 2016, and the second generation arrived in 2020. Of course, we ... Read more »

Watch Spain vs. Germany at World Cup 2022 From Anywhere

Spain vs. Germany is one of the biggest matches of the World Cup group stage. Read more »

Get Hulu for Only $2 a Month With This Cyber Monday Deal

Save $72 on a full year of Hulu and stream Andor, The Bear, Only Murders in the Building and more. Read more »

What is Kali Linux and Do You Need It?

If you have heard a young would-be hacker talking about penetration testing, chances are, you would inevitably hear what Kali Linux is. It is a Debian-based Linux distribution that aims ... Read more »

Bayonetta 3 Is On Sale For A Great Price At Amazon

Save $15 on one of the best action games of 2022. Read more »

Which Streaming Service Has the Most 4K Content?

Tada Images / Shutterstock.com Netflix, Disney+, and Amazon Prime Video, offer the most 4K content among major streaming services. 4K content used to be hard to come by on streaming ... Read more »

LIVE: Croatia vs. Canada - Game thread, preview, how to watch

Two legendary veteran midfielders for their countries: Luka Modrić of Croatia (left) and Atiba Hutchinson of Canada (right). 10:16 am EST – DONEIL HENRY SIGHTING! Looks like he’ll stand with ... Read more »

Fire Emblem Warriors: Three Hopes Is Only $30

Slay enemies and recommended retail prices with this big discount on the Fire Emblem spin-off. Read more »

How to evolve Applin into Flapple in Pokémon Scarlet & Violet

Image by The Pokémon Company Dragon-type Pokémon can look like anything nowadays, and most of them can be found in Pokémon Scarlet & Violet. Included among these are one of ... Read more »

How to Get Ready for New TFSA Changes in 2023

work from home Some exciting news was just announced for Canadians who regularly invest in their Tax-Free Savings Account (TFSA). The Canada Revenue Agency (CRA) has increased the 2023 TFSA ... Read more »

LG’s New Car Tech Could Change the Way You Hear Music

LG Display LG Display, the innovation lab of LG Electronics that’s created things like stretchable displays, just announced a new invisible speaker technology for automobiles. LG can integrate its all-new ... Read more »

Talking turkey: Need help? Here’s how to salvage your Thanksgiving with these tips, tricks and a hotline

HOUSTON – Need help this Thanksgiving? There is assistance, but you need to know where to look. Use this as your jumping-off point to help get your Thanksgiving on-track, or ... Read more »

How to Remove Personal Information from Files on Windows 11

Your documents and other files include personal metadata. If you want to remove your personal information from files on Windows 11, follow this guide. When you create a file on ... Read more »

Xiaomi 13 launches on December 1, design has been tipped

It’s not a secret anymore that Xiaomi prepares to launch the Xiaomi 13 series. The new devices are close to their release and appeared on multiple certification bodies. Now, a ... Read more »

SCAR: The SOCOM Rifle that became a Fortnite Icon - Loadout

Since the early 2000s, the SCAR-L and SCAR-H Assault Rifles have become mainstays in our video games. But what are the differences between the two, how did they become such essential additions to our gaming arsenals, and do they deserve their place among other iconic assault rifles like the M16 and AK-47? Read more »

Instructions on how to save phone numbers to sim on iPhone

How to save phone numbers to sim on iPhone is the concern of many users when using this device. If you don’t know how to do it, don’t skip the ... Read more »

U.S bans new equipment sales from Huawei, ZTE and three other Chines firms

The U.S government is banning approvals of new telecommunications equipment sales from five Chinese firms. According to Reuters, the U.S Federal Communications Commission (FCC) is restricting the sale or import ... Read more »

How to evolve Applin into Appletun in Pokémon Scarlet & Violet

Image by The Pokémon Company The region of Paldea is chock-full of different dragon-type Pokémon to discover. One of the many that you can encounter is the apple trio of ... Read more »

The Sun’s Who Cares Wins: how to watch awards show hosted by Davina McCall – what time is it on Channel 4?

The Sun’s Who Cares Wins, an awards show for healthcare workers, will air on Channel 4 on Sunday 27 November. The ceremony, which took place in London earlier in the ... Read more »

OpenAI successfully trained a Minecraft bot using 70,000 hours of gameplay videos

Why it matters: Minecraft may not sound like an important tool that supports advanced AI research. After all, what could possibly be so important about teaching a machine to play ... Read more »

How to watch Canada vs. Croatia in the 2022 FIFA World Cup today

A loss today means Canada's hope of advancing to the knockout round/round of 16 evaporates Read more »

How to evolve Crabrawler into Crabominable in Pokémon Scarlet & Violet

Screenshot by Pro Game Guides There are tons of favorite crab-like Pokémon throughout Pokémon Scarlet and Violet, and Crabrawler and Crabominable are two of them. When the two were introduced ... Read more »

Debate world champion explains how to argue - Big Think

jQuery(function(){ if (typeof jwplayer_load == typeof indefined) { var jwplayer_load = 1; jQuery(window).load(function(){ jQuery(".sc_video_shortcode_jwplayer").each(function(){ var _this = $(this); jQuery.get(jQuery(this).attr("url"), function(data, status){ if (typeof data.split("\n") !== typeof undefined) { var src ... Read more »

Nissan Micra (Mk2, 1992-2002) icon review

We take a second look at the Nissan Micra supermini in second generation K11 form Read more »
Breaking thailand news, thai news, thailand news Verified News Story Network