How to separate address in Google Sheets

When you have a list of addresses in a single column, separating them into individual columns (e.g., street address, city, state, postal code) makes it easier to generate mailing labels or perform mail merges. It ensures that each part of the address is correctly placed in the designated field for accurate and efficient communication. Separating addresses into distinct components like street, city, and state enables geocoding, the process of converting addresses into geographic coordinates (latitude and longitude). Geocoded data can be used for mapping purposes, spatial analysis, or visualizing address data on maps.

The provided dataset consists of a list of addresses, each containing the full address in a single column. The addresses follow a consistent format, with the house number, street name, city, and state separated by commas. We will learn in this tutorial, how to separate addresses into columns.

Method 1 – By using SPLIT Function

In this method, we will use the SPLIT Function to get our desired results.

Understanding the Syntax of SPLIT Function

The split function in Google Sheets is used to separate a text string into multiple parts based on a specified delimiter. The function takes two arguments: the text string to be split and the delimiter that determines where the splitting should occur.

Here’s the syntax for the split function:

=SPLIT(text, delimiter)

Where,

text: This is the text string that you want to split into parts.

delimiter: This is the character or string that acts as the separator or delimiter for splitting the text. When the delimiter is found in the text, the string is split into parts at that delimiter.

Step 1 – Select the cell

• Select the cell adjacent to full address.
• In this cell, we will utilize the SPLIT Function to separate the address.

Step 2 – Writing the formula and implementing it.

• Start by entering the equal sign (=) to initiate the formula.
• Next, type “SPLIT” and use the tab key on your keyboard to select the SPLIT function which takes three parameters.
• In the first parameter, type the cell name to specify the text string that you want to split. In this case, it is the value in cell A2.
• Insert a comma (“,”) to separate the parameters.
• The second parameter is the delimiter, which determines where the splitting should occur. In this case, we have entered the delimiter is a comma (“,”) because our addresses have this delimiter to separate the components of address.
• Insert another comma (“,”) to separate the parameters.
• The third parameter, “split_by_each” which divides text around a specified character or string and puts each fragment into a separate cell in the row. In this case, we set it to TRUE, indicating that the text will be split into individual characters.
• Then, add a closing parenthesis.
• After following all the steps above, the final formula would look like this:

=SPLIT(A2,”,”,TRUE)

• Then, press Enter and your formula will be implemented.

Step 3 – Implementing the formula to whole range

• Select the cell in which the formula is implemented and move your cursor to the bottom right corner of the cell. For instance, it is cell B2.
• Then, double-click on the fill handle to implement the formula on whole range.

Method 2 – By using Split text to columns feature

In this method, we will use the “Split text to columns” feature to separate the addresses

Step 1 – Copy and select the cells

• Copy all the cells in which addresses are present.
• Then paste those cells in the adjacent column because Google sheets will utilize this column also while separating the components of addresses.
• Make sure that the copied cells are selected.

Step 2 – Separating the addresses

• After selecting the cells, navigate to the “Data Tab” and click on it.
• A list of options will appear on your screen.
• From this drop-down menu select option named “Split text to columns”.
• As soon as you click on it, it will automatically detect the delimiter present between addresses and will write them in separate columns.