how to split cell horizontally in google sheets

In this tutorial you will learn to split cell horizontally in google sheets.

When working with a spreadsheet we come across some common tasks on a daily basis. One of such tasks is splitting cells in a spreadsheet. 

Below are some practical use cases:

• You have a sheet with a list of names and you want to split it into First name and last name. Or even last name or first name
• You have a column with address data from an e-commerce store or your employees. You want to split the full address into separate data points like State, Zip Code, City, etc
• A column with a price list but with the currency symbol ($) in it. You need to split it into 2 cells to have the price in one cell and the currency symbol in next

You got the idea. These are some of the common tasks if not some advanced tasks like splitting a paragraph having a special character in it.

In this tutorial, we will explore how to split cell horizontally in google sheets. Then we will explore how to do it vertically, diagonally, and in other ways. Let’s jump in.

Table of contents

• Using Split Formula
• Using Menu shortcut for split cells 
• Split vertically
• Split cell by comma
• Split cell with Query formula 
• FAQ and examples

In google sheets, you can use 2 methods to split a cell. Using the SPLIT() function and the inbuilt “Split text to columns” action in the menu bar.

Split cell horizontally in google sheets using the formula:

The split function in google sheets divides text around a certain string or character and puts each fragment of the divided text into different rows.

Syntax

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

Input

text – The text to split.
delimiter – Define the string or character where you need to split the text.
split_by_each – [ OPTIONAL – TRUE by default ] 

If TRUE then each character in the delimiter string will be considered while splitting
Example:- If the delimiter is the word “fox”, then the sentence will be split wherever it finds “f”, “o” and “x”

If FALSE then the delimiter will split the sentence only when it finds the whole word “fox” 

remove_empty_text – [ OPTIONAL – TRUE by default ]

Whether or not to remove empty text messages from the split results. The default behavior is to treat consecutive delimiters as one (if TRUE). If FALSE, empty cell values are added between consecutive delimiters.

Here is a quick example to understand this better

Here the SPLIT function will split the text in A1 into two parts. Before and after the word “cell”. 

The split_by_each parameter is set to FALSE. This will tell google sheets to consider the whole text as one delimiter.

Try yourself what happens when you make the third parameter in this example TRUE instead of FALSE.

You can achieve this same result if you want to use the shortcut from the menu.

Split cell horizontally in google sheets using the menu shortcut

You can also split the text in a cell horizontally using the menu shortcut.

Here is a demo animation showing this in action:

Google sheet recently introduced this shortcut to split text. And it comes in very handy.

Example – Split cell horizontally in google sheets

You just learned how to split cell horizontally using the SPLIT formula and menu shortcut.

Let’s explore one real-life example.

In the below table, the serial number and employee name are written in one column. We want to split them horizontally to have the serial number and name side by side.

As you can see using the =SPLIT(A1,”-”) formula, we can split A1 into two columns. One containing the serial number and another with the employee name. 

Using the menu shortcut method, you can do the same. Below is a quick animation showing the same:

Go ahead and try to do this for other cells in column A.

Split cell vertically in google sheets

You learned how to split cell horizontally in google sheets. Now let’s see how to split cells vertically.

We have a text in cell A1 which has employee names separated with “|” symbols. We want to split and list the names vertically like below:

Here is how to do it:

Use the SPLIT function with the TRANSPOSE function to split cells vertically in google sheets

The formula is =TRANSPOSE(SPLIT(A1, “|”))

Apply this formula in cell C1 to get the output as shown in the picture.

Split cell by multiple characters 

Using the same SPLIT() formula, you can split a text in a cell at multiple characters.

In the below example, the text in cell A1 first splits at character “ ‘ ” and then at character “ ? ”.

The key to splitting with multiple characters is to ensure the split_by_each parameter is set to TRUE. Though it’s TRUE by default, make sure it is.

Example – Split by a comma in google sheets

Let’s say you have a cell with text like this:

To split the text in cell A1 at each comma character, you can use the menu shortcut method or the SPLIT formula method.

Let’s use the menu shortcut method.

Select the text and then click on Data > Split text to column. Google sheets will automatically detect and split the text in cell A1 into multiple names and will fill them into adjacent columns.

If it does not detect automatically, you can choose the “Comma” option from the Separator drop-down.

This is how you can split a cell text having commas in them.

Similar to this example of splitting cells by comma, you can now split cells by any characters you need.

Split cell with ArrayFormula and Query formula

Consider the below list of data in cell A.

Some rows have age in them and some do not. And you want a list of only names and genders.

Let’s see, how can you achieve this?

If you use the regular SPLIT formula to split cells by space, then you will get the results like this.

Since we do not want the third column, we can include a small query formula to select only Columns 1 and 2 from the result and display them.

Here is the formula:

=query(arrayformula(split(A1:A5, " ")),"select Col1, Col2, Col3")

And here is the output

Here the array formula will ensure the formula is applied to all cells at once and you do not have to drag the SPLIT formula across rows.

The query formula will select Col1 and Col2 from the results and that will be the output of this combined formula.

Frequently asked questions and examples

Wrapping up

In this guide, you learned how to split cell horizontally in google sheets. Also, you saw examples of splitting cells vertically, with commas, with special characters, and many other ways. 

You also learned how to use the SPLIT function in combination with ArrayFormula and Query function. 

As you work with more unstructured data in google sheets, these methods will come in super handy to structure and format your data.

Appendix

[1] SPLIT formula in google sheets – Link

Further Reading

New to google sheets ? Start here