How to split cells in Google Sheets

1. Standard tool to split text to columns in Google Sheets

You can split cells in Google Sheets with the standard built-in tool. It's called Split text to columns. Though it won't do for complex tasks, it's useful enough to separate text in Google Sheets by one delimiter at a time.

1. Select the cells you want to split.
2. Go to Data > Split text to columns:
3. A floating pane will appear at the bottom of your sheet:

   It offers some of the most commonly used separators: comma, semicolon, period, or space. But you can also enter a Custom one or have Google Sheets detect it for you automatically (Detect automatically).

4. Pick the delimiter you have in your data, for instance space.

Google Sheets will immediately separate text into multiple adjacent columns by that space:

2. Split cells in Google Sheets by character

The next tool is the quickest and most convenient way to split cells in Google Sheets.

It's called Split text and you will find it in the Power Tools add-on for Google Sheets.

It splits your cells in Google Sheets at each occurrence of all delimiters. There are a whole lot of various separators available:

• standard ones: space, line break, comma, semicolon
• custom symbols (for you to enter)
• text strings & conjunctions like and, or, not, etc.
• even capital letters

Here's why this Split Text is better than the standard Google Sheets tool:

• In case one delimiter follows the other right away, the extension will treat them as one if you tell it. Something the standard tool from Google Sheets cannot do ;)
• You also control whether to replace your source column with the first part of the data. Another thing the standard splitting tool cannot do ;)
• And a cherry on top? You can even split to rows!

Having all these features doesn't mean that the add-on is complicated. On the contrary: it's beginner-friendly. Here's how you use it to split cells in Google Sheets:

1. Select those cells you need to split in Google Sheets.
2. Set up all characters to separate by.
3. Adjust the settings at the bottom (it's just 2 checkboxes).
4. And click the Split button:

In my case, the add-on automatically inserts 2 new columns — D and E — and puts the results there. Hence, my columns with numeric data remain intact.

3. SPLIT function for Google Sheets

In your quest to master the art of splitting cells in Google Sheets, I cannot help but mention the SPLIT function. It also divides text into adjacent columns based on a chosen delimiter.

The function takes the following form:

• text is your cell (or cells, or simply text string) that you want to split. Required.
• delimiter is the character by which you want to separate the text. Required.
• split_by_each — controls whether you cut by each instance of the delimiter (TRUE) or only at the first one (FALSE). This one is optional and works as TRUE if you omit it.
• remove_empty_text is also optional. It determines whether to include or exclude empty values in the resulting cells. They may occur if there are consecutive delimiters in a cell. So basically this one is all about whether to treat consecutive delimiters as one (TRUE, the default one) or no (FALSE).

Let me take the same column with product names and split them into separate columns using the SPLIT function:

1. Here's what I enter to D2:

   =SPLIT(C2," ")

2. I also copy the formula down column D to do the same for each row.

The function separates my product names by each space and places units to adjacent cells.

4. Other Ablebits tools to separate text in Google Sheets

Apart from splitting text by characters, Power Tools from Ablebits contains other ways to split that data: by position, separate first and last name, and also split date and time!

Click the button below to install Power Tools to your Google Sheets.

4.3 Split date and time

While none of the tools above process dates, Power Tools has an ace up its sleeve. It has one more little helper that separates time units from date units if they are both written in a celll, e.g. 6/9/2020 9:00:00:

The tool is called Split Date & Time:

Here's how you split date & time in Google Sheets using this tool:

1. Select the column with Date time values.
2. Tick off those columns you want to get as a result: both date and time or only one of them to extract from the column.
3. Click Split.