Maruf Niaz
When working with addresses in Google Sheets, sometimes you may need to split a large address into its individual components such as street, city, state, and zip code. Knowing how to split addresses can help you in Organizing and analyzing address data in Google Sheets. In this article, we will show you several methods for how to split an address in Google Sheets.
The above screenshot is an overview of the article, representing how we can split an address in Google Sheets.
A Sample of Practice Spreadsheet
You can download the spreadsheet and practice it.
3 Easy Methods to Split Address in Google Sheets
Method 1: Using Split Text to Columns Feature
Splitting address data can be done very easily and quickly using the Split text to columns feature in Google Sheets. To accomplish this task, consider using the following dataset that contains the Store Addresses of a company. We will split these addresses into Street, City, State, and Zip Code using the built-in Split text to columns feature in Google Sheets.
Steps:
- First, select the address cell range which you want to split. We select the range B6:B15 in our example.
- After that, go to Menu bar> Data > Split text to columns.
- Afterward, select the appropriate separator in the Separator dialog box to divide the address components. In our example, the addresses are structured as “Street, City, State, Zip Code,” so we select the Comma as the separator to split the addresses.
- Finally, you will see that the addresses are split into different columns.
Read More: How to Split Text to Columns Using Formula in Google Sheets
Similar Readings
- How to Split View in Google Sheets (2 Easy Ways)
- [Solved!] Split Text to Columns Is Not Working in Google Sheets
- How to Split Cell by Comma in Google Sheets (2 Easy Methods)
- Split a Cell in Google Sheets (9 Quick Methods)
Method 2: Utilizing SPLIT Function with ARRAYFORMULA
Another way to split a string into arrays is by using the SPLIT function with the ARRAYFORMULA function.
The SPLIT function allows you to split an address into its component parts using a specific delimiter. The ARRAYFORMULA function allows you to apply a function to a range of cells.
We use the same dataset we used for the previous method. We will use the SPLIT function together with the ARRAYFORMULA function to split these addresses into Street, City, State, and Zip Code.
Steps:
- First, go to the first cell of the table where you want to split the addresses. We go to cell C5 in our example.
- Then, insert the ARRAYFORMULA function.
- Then, insert the SPLIT function.
- After that, type the address range that you want to split. We type B5:B14 as the address is in range B5:B14 in our example.
- Now, type the delimiter by which you want to split the address. In our example, the addresses are structured as “Street, City, State, Zip Code,” so we insert Comma(,) inside a quotation as the delimiter.
- Then, close the parenthesis to complete the formula. This is what the final formula looks like:
=ARRAYFORMULA(SPLIT(B5:B14,","))
- Finally, press ENTER to split the address.
Read More: How to Split String into Array in Google Sheets (3 Easy Methods)
Method 3: Applying SPLIT and ARRAYFORMULA with QUERY Function
The QUERY function can be used with the SPLIT function to split an address into its components.
We use the same dataset we used for the previous methods to split these addresses into Street, City, State, and Zip Code applying the SPLIT function together with the ARRAYFORMULA and the QUERY functions.
Steps:
- First, go to the cell Where you want to apply the formula. We go to cell C5 in our example.
- Then, type the following formula:
=QUERY(ARRAYFORMULA(SPLIT(B5:B14,",")),"select Col1, Col2, Col3, Col4")
Formula Explanation:
- SPLIT(B5:B14,”,”)
First, the SPLIT function splits the addresses.
- ARRAYFORMULA(SPLIT(B5:B14,”,”))
Then, the ARRAYFORMULA will split the addresses in range B5:B14
- QUERY(ARRAYFORMULA(SPLIT(B5:B14,”,”)),”select Col1, Col2, Col3, Col4″)
Finally, the QUERY function will show the result of the split addresses in columns 1, 2, 3, and 4.
- Finally, press ENTER to show the results.
Read More: How to Use QUERY with SPLIT Function in Google Sheets
Conclusion
In this article, we have demonstrated ways to split address data in Google Sheets. In order to fully grasp the concept, practice the techniques demonstrated in this article. The goal of the article is to provide helpful information and guide you in your task.
Additionally, consider looking into other articles available on OfficeWheel to expand your understanding and skill in using Google Sheets.
