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.
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: 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. 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.
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).
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:
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:
