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.
- Select the cell whose contents you want to split. To split multiple cells, hold and drag the cursor over the desired cells.
- Then, click the Data menu at the top and select the Split text to columns option.
- On the small Separator prompt, click Detect automatically and select a suitable delimiter.
- 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 “–“.
- Your split text output should now appear on the right side of the selected cell.
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.
=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:
- Select the destination cell where you want to display the split output.
- 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.
- After entering the necessary arguments, press Enter, and the split output will appear next to the selected cell.
- 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.
- 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.
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.
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.
- 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.
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).
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:
- Click the Extensions menu and select Add-ons > Get add-ons.
- 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:
- Select the cell where you want the output to appear.
- Enter the necessary arguments in the SPLIT function as mentioned above.
- Next, enclose it with the TRANSPOSE function. For instance, TRANSPOSE(SPLIT()).
- Press the Enter key, and your split output will be displayed vertically on a single column.