Let’s say you’ve got an address written out in a single cell, for example, “123 Main Street, Springfield, IL, 62701”. What if you separated the street address, city, state, and zip code into different cells?
How can we do that?
Thankfully, Microsoft Excel makes it easy to tackle this task through various built-in functions and features.
To separate an address in Excel, you can use text functions such as CHAR, FIND, LEFT and RIGHT functions and other available text manipulation functions to extract different parts of the address.
With the right combination of these functions, you can efficiently divide a full address into separate cells for street, city, state, and zip code.
Read on to learn how to make full use of these tools!
Basics of Separating an Address in Excel
To split an address, you’ll use a combination of text functions like LEFT, RIGHT, MID, and FIND. You’ll need to find the position of certain “separator” characters, such as a comma (,).
The general process for splitting an address is as follows:
Identify separators: Determine the characters (e.g., comma, space, hyphen) that separate the different parts of the address (e.g., street, city, state, and ZIP code).
Find separator positions: Locate the positions of the separators in the address using functions like the FIND function and LEN function. The FIND function will return the position of the specified character within the text string.
Split addresses: Use text functions like LEFT, RIGHT, and MID to extract specific parts of the text based on the positions of the separators.
Clean up the results: Once the separate parts of the address have been extracted into different cells, consider using the TRIM function to remove any extra spaces.
Now that you know the steps, we’ll look at step-by-step methods for separating addresses in Excel in the next section.
How to Select Each Part of the Address for Separation
We’ll begin by looking at how to separate address components such as the street number, street name, city, state, and postal code.
1. Separating Street Number and Street Name in Excel
To separate the street number and street name from a complete address in Excel, you can use a combination of Excel functions like LEFT, SEARCH, and MID. These functions are practical for parsing standard address formats.
Consider the following example where we have addresses in one column:
Example Address: “123 Main Street, Springfield, IL, 62701”
The goal is to extract the “Street number and street name” part, which, in this case, is “123 Main Street.”
The formula for Extraction
=LEFT(A1, SEARCH(",", A1) - 1)This formula will return “123 Main Street” from the example address.
Explanation of the Formula
SEARCH(“,”, A1): Finds the position of the first comma in the address, which typically follows the street name.
LEFT(A1, SEARCH(“,”, A1) – 1): Extracts all characters from the beginning of the string up to the position just before the first comma, effectively separating the street number and name.
Enter the below formula and press the Enter key.
=LEFT(A2, SEARCH(" ", A2) - 1) & " " & MID(A2, SEARCH(" ", A2) + 1, SEARCH(",", A2) - SEARCH(" ", A2) - 1)This formula will return “123 Main Street”.
Use the formulas below to separate the data into street numbers and names.
2. Separating Unit Number and Street Name in Excel
You can also separate an apartment or suite number with a street name from one cell.
Let’s take an example of some address in one column.
Explanation:
=LEFT(A2, LEN(A2) - LEN(TRIM(RIGHT(SUBSTITUTE(A2, " ", REPT(" ", LEN(A2))), LEN(A2)))) - 1)This formula will return “Asna Place Main Road”.
To extract the apartment or suite number, use the below formulas.
=TRIM(RIGHT(SUBSTITUTE(A2, " ", REPT(" ", LEN(A2))), LEN(A2)))This formula will return “15”.
3. Separating City State, and Zip Code
The formula for the city name
=TRIM(MID(A2, FIND(",", A2) + 2, FIND(",", MID(A2, FIND(",", A2) + 2, LEN(A2))) - 2))To separate the city, state, and ZIP code, you can use the combination of TEXTJOIN (in Excel 2019 or later) and FILTERXML functions, as discussed above.
=TRIM(RIGHT(A2, LEN(A2) - FIND(",", A2, FIND(",", A2) + 2)))To separate the zip code, use the below formulas.
Now, let’s look at a more advanced method, using Excel formulas and a custom function to separate an address like a street number.
Advanced Method for Separating an Address
We can also use a custom splitting address function and Excel formulas to extract a specific piece of information, a street number and street name, from an address.
To do it, we can use the following steps:
Write a custom UDF to separate components: Start by writing a custom function in Excel that takes the full address as input and extracts the street number and street name from it.
Test the UDF: Once the function is written, test it on a few sample addresses to ensure it works as intended.
Split the Address: Apply the custom function to the entire column to split the street number and name for the highlighted addresses.
Steps to Create and Use a Custom UDF:
Write a Custom UDF:
- Open Excel and press ALT + F11 to open the Visual Basic for Applications (VBA) editor.
Insert a new module and write a UDF named SplitAddressTest that takes two arguments: the complete address and an index number indicating which part of the address to extract (1 for street number and name, 2 for city, etc.).
Function SplitAddress(completeAddress As String, indexNum As Integer) As String
Dim addressParts As Variant
addressParts = Split(completeAddress, " ")
' Assuming the format is always [Street Name] [Street Type] [Unit Type] [Unit Number],
' and you want to split these out. Adjust the logic if the format changes.
Select Case indexNum
Case 1 ' Street name and number
SplitAddress = addressParts(0) & " " & addressParts(1)
Case 2 ' Unit type
If UBound(addressParts) > 2 Then
SplitAddress = addressParts(2)
Else
SplitAddress = ""
End If
Case 3 ' Unit number
If UBound(addressParts) > 3 Then
SplitAddress = addressParts(3)
Else
SplitAddress = ""
End If
Case Else
SplitAddress = "Invalid index"
End Select
End FunctionThe UDF should parse the address string based on commas or other delimiters and return the requested component.
Test the UDF:
Test the SplitAddress function in Excel by applying it to sample addresses. Ensure that it correctly extracts the desired address component.
Apply the UDF to Split Addresses:
Use the UDF in your Excel sheet by applying it to the column containing addresses. For example, use =SplitAddress(A2, 1) to extract the street number and name from the address in cell A2.
Formulas that you can use to separate the address components:
Street number and street name: =SplitAddress(“123 Main Street, Springfield, IL, 62701”, 1)
City: =SplitAddress(“123 Main Street, Springfield, IL, 62701”, 2)
State: =SplitAddress(“123 Main Street, Springfield, IL, 62701”, 3)
ZIP code: =SplitAddress(“123 Main Street, Springfield, IL, 62701”, 4)
If you like what you see, you’ll love the next section, which covers importing and separating data using Power Query.
How to Import and Separate Data in Excel Using Power Query
Power Query, a free desktop feature of Excel, can be handy when importing and separating addresses.
This section will cover how to work with Power Query to make importing data and separate addresses easier.
1. Preparing Your Data
First, you need to start with a well-prepared dataset in Excel. The table(s) you work with should be clear and organized, with a distinct column for each address element, such as street, city, state, and zip code.
You can also combine elements of the address, like street name and number or city and state, into a concatenated cell before importing it into Power Query.
2. Importing Data
To start, ensure your cursor is in a cell within the dataset. Then, navigate to the ‘Data’ tab, click ‘Get & Transform Data’, and choose ‘From Table‘ or ‘From Range‘ depending on your data source.
Alternatively, if your data is in an external source (e.g., a CSV file or SQL database), you can click on the ‘From File’ or ‘From Database’ options.
Power Query will launch, and you can start working with your data.
3. Splitting the Address
Load the data into Power Query.
Find the column where the address is located.
Right-click on the column to “Split Column” and “By Delimiter.”
Select the delimiter that separates the different elements of the address (e.g., comma or space).
- Repeat the same for all needed address elements (e.g., street, city, state, and zip code).
The ‘Split Column’ feature in Power Query makes separating and analyzing extensive datasets, such as address information, more straightforward.
By following this method, you’ll save significant time and effort compared to manually separating the address elements in Excel.
Having address data separated into different columns will allow you to manage and analyze the data more efficiently.
How to Separate Data Using Text to Columns
We just covered how to separate address elements in the previous section. This section will take that one step further with the text-to-columns feature.
Imagine you have an address format like Street/Place Number Street Name, For example, Shahjahan Road Number 13A.
You can use the Excel text-to-columns feature to separate these data elements, which helps break down text-based data by splitting cells based on a specified character or delimiter.
Let’s look at how you can use this feature to separate addresses.
1) Splitting Address Components by Delimiter
- Click on the column heading that contains the addresses you want to split. It should be the column containing the full address of the desired data set.
Go to the Data tab in the Excel ribbon and locate the ‘Data Tools’ group.
Click on the Text to Columns button.
In the Text to Columns Wizard, select Delimited.
Click Next and select the appropriate delimiter(s), such as commas, (spaces), or dashes, that separate the address components: street, city, and ZIP code.
Click Next and, if required, set the column data format for each address component.
Click Finish to split the addresses into separate columns.
You can use the Convert Text function method if you make a mistake while splitting. Read the next section on when to use the CONVERT function to learn how!
Final Thoughts
Excel offers a range of methods to efficiently separate addresses into individual components. These methods leverage Excel’s powerful text manipulation and data processing capabilities, allowing users to easily parse and organize address information.
Whether using basic functions or more advanced techniques, Excel simplifies the task, making it accessible for both beginners and experienced users.
Lastly, the ability to break down complex data into manageable parts is one of the key strengths of Excel, making it an invaluable tool for data management and analysis.
Wanna learn more about some Powerful Features in Power Query? Check out the EnterpriseDNA YouTube channel:
Frequently Asked Questions
How do you separate addresses by lines in Excel?
To split an address into separate lines in Excel, follow these steps:
Select the cell containing the address or the range of cells with multiple addresses.
Go to the Data tab on the ribbon.
In the Data Tools group, click Text to Columns.
This will open the Convert Text to Columns Wizard. Choose Delimited or Fixed Width based on your data format and proceed accordingly.
How do you break a full address into different columns?
Breaking a full address into different columns can be achieved using the Text to Columns functionality.
First, select the cell containing the address, click on the Data tab in the ribbon, and choose Text to Columns.
From there, follow the Convert Text to Columns Wizard steps to separate your address into distinct columns.
What function can be used for extracting city names from addresses?
You can use the LEFT, FIND , and LEN functions to extract city names from addresses.
The following formula can be used:
It operates based on the premise that the city name comes after the first comma and space in the address.
How do you split the city, state, and ZIP code into separate cells?
Following the steps in the first question, you can use the Text to Columns functionality to split city, state, and ZIP codes into separate cells.
This will split the selected column into separate cells containing the city, state, and ZIP code from the addresses.
How do you divide an address string into building and street?
You can combine LEFT, RIGHT, SEARCH, and LEN functions to divide an address string into building and street.
The following formula can be used:
What is the best way to split a multi-line address into separate lines?
To split a multi-line address into separate lines, you can use the Text to Columns functionality following the steps in the first question.
When you reach the “Delimited or Fixed Width” step in the Wizard, check the “Treat consecutive delimiters as one” checkbox if you have multiple spaces or other separators between the different parts of the address.
Is it possible to break a city street and state into different cells?
You can use the Text to Columns functionality to break a city street and state it into different cells.
First, follow the steps in the first question to split the street address from the city and state.
Then, you can use the answer to the third question to separate the city and state into separate cells.
How do you extract ZIP codes from cell ranges?
The exact Text to Columns functionality can extract ZIP codes from cell ranges.
Select the range of cells containing the addresses, click on the Data tab in the ribbon, and choose Text to Columns.
Ensure you follow the Wizard steps correctly to split the street address, city, state, and ZIP codes into separate columns.
