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.
SPLIT(text, delimiter, [split_by_each], [remove_empty_text])
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.
To split text to the column using the menu shortcut :
1. Open google sheets on your computer and select the cell to split
2. Click on Data > Split text to columns
3. Choose the Separator from the drop-down options
4. Use a custom option to split using certain words or character
5. The text in the cell will split horizontally into columns
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.
|Alan Male 29|
|Ana Female 27|
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
How do you split first and last names within the same cell in google sheets?
You can split cells with first and last names by using space as the delimiter.
Here is the formula and example
Note: You can use the array formula which will apply automatically for all cells in Column B.
How to split a cell in google sheets into last name and first name?
We saw how to split the cell by First and then the Last name. To do the reverse, you can use the QUERY formula.
Here is the formula:
=query(split(A1:A5, ” “),”select Col2, Col1”)
And here is the result in action:
How do I split only the dollar amount from one cell to their own cell in google sheets?
To split a cell with a dollar as the delimiter you can use the same SPLIT formula and choose to have the dollar symbol ($) as your delimiter.
In google sheets split the number at beginning of the cell?
To split the text in a cell at the beginning of the cell, you need to use the SPLIT formula in combination with either the RIGHT or LEFT formula to pass the position of the character in the text.
How to split cell after 4 characters in google sheets ?
Here is the formula:
And here is the demo:
The LEFT function tells the SPLIT formula to split at position 4 from the left-hand side. In this case, that character is “-”.
How to split the date and time in a DateTime cell google sheets?
You can split the date and time in a cell using the SPLIT function with space as the delimiter. Or in case the date and time are written using some other special characters, you can use that as the delimiter.
how to split a cell in reverse order in google sheets?
To split cells in reverse order, first split them in the normal order and use the QUERY formula to select the data in the reverse order.
Refer to the example of Last name and First name shown in this guide.
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.
 SPLIT formula in google sheets – Link
New to google sheets ? Start here