Today, I’ll show you how to separate addresses in Excel in various methods so you can choose one that meets your requirements and Excel usage style.

Working with addresses in Excel can feel just as overwhelming, especially when they’re all stuffed into a single cell. It’s a common challenge, and without the right know-how, it can eat up your time and patience. But don’t worry—I’ve got you covered! In this tutorial, I’ll guide you through simple, step-by-step methods to split addresses effortlessly. Let’s dive in!

Separate Address Using the Flash Fill Tool

Flash Fill is a pattern-recognizing algorithm that scans the changes you make in your dataset, senses if there’s logic in the data edits, and finally replicates the same edits to automate the task. This feature is available in Excel 2016 and newer editions.

Let’s assume, you want to extract the text strings in a full address into columns, like Street, City, State, and ZIP, just like the worksheet shown in the screenshot above.

So, go to the source dataset, and manually type in the address parameters in their respective columns. Do this for at least the first 3 rows of the whole dataset.

Now, select the first column, Street, from C2 to C11 or until the last cell in the column where data exists in the adjacent row to the left.

Press Ctrl + E to use the Flash Fill tool.

Excel will only add the street address text strings from the full address column in the Street column.

Subscribe to How to Excel!

Get 3 FREE eBooks, the latest posts, all example files, and more from How to Excel straight to your inbox.Subscribe

We use your personal data for interest-based advertising, as outlined in our Privacy Notice.

Now, repeat the above steps for other columns, like City, State, and Zip.

That’s it! You’ve successfully used the Excel Flash Fill tool to split addresses.

Separate Address Using the Text to Columns Tool

Text to Columns is a universally available data analysis tool in all Excel editions since Excel 97. So, it’s the most commonly used technique to split large address strings into shorter components, like street, city, state, ZIP, etc.

You can use Text to Columns if the full address is separated using any of the following characters:

• Delimited text strings with characters like Tab, Semicolon, Comma, Space, and anything else that you can indicate using the keyboard.
• Fixed-width text strings where address fields are organized in columns and separated by one or more spaces.

To use Text to Columns, go to the source worksheet and select the exact dataset you want to split into different columns.

Press Alt > A > E on the keyboard to activate the Text to Columns wizard.

If there are spaces between the parts of the address, choose Delimited.

Contrarily, choose Fixed width when there are a fixed number of spaces between the address data string components.

Click Next after choosing an appropriate Text to Columns data delimitation pattern.

Since the test dataset I’m using contains commas, I’ve chosen Delimited.

On the Convert Text to Columns Wizard – Step 2 of 3, you’ll need to choose the exact character used as the delimiter, for example, Comma.

Now, look at the Data preview section on the Text to Columns dialog box.

If you see that the various components of the address are separated in columns clearly, click Next.

You should now see the 3rd dialog box of the Text to Columns wizard where you can choose the data formatting output, like General, Text, Date, etc.

Select one option below the Column data format section and click on the Finish button.

Excel will split the addresses into different columns on the active worksheet.

You can now enter column header texts above the newly generated columns to further organize the address parts.

Use an Excel Function to Split the Address

If you face issues in splitting addresses in Excel using the first two methods since the address string isn’t regularly formatted, you can use some Excel functions. The most commonly used functions are LEFT, MID, and RIGHT.

Using the LEFT Function

Suppose you only need to extract the street address from a full address. Use the following formula syntax for the LEFT function:

=LEFT(B2,FIND(",",B2)-1)

In the above formula,

• B2 is the cell containing the address string. Change the cell reference accordingly.
• FIND(",",B2) locates the position of the first comma.
• Subtracting 1 ensures only the street address is included.

For example, I entered the formula in C2.

Upon hitting the Enter key, Excel extracted the street address accurately.

Then, I used the fill handle in C2 and dragged it down to apply the formula to the rest of the cells of column C.

The formula separated the street address part for all the rows.

Using the MID Function

Now, when it comes to splitting up the given address and extracting the city name from the middle of the address string, you can use the following MID formula:

=MID(B2,FIND(",",B2)+2,FIND(",",B2,FIND(",",B2)+1)-FIND(",",B2)-2)

Here’s an explanation of the above formula so you can customize it according to your own dataset:

• FIND(",",A1)+2 finds the start of the city, which is two characters after the first comma (accounting for the space after the comma).
• FIND(",",A1,FIND(",",A1)+1) finds the position of the second comma, which marks the end of the city.
• Subtracting the positions gives the length of the city name.

I used this formula in D2 to get the city name Springfield.

Then, I used the drag handle to apply the formula to the rest of the column.

Excel has successfully extracted the city name from the given full addresses in column B.

Using the RIGHT Function

Finally, to separate the state name and ZIP code from the full address, you can use this RIGHT function:

=RIGHT(B2,LEN(B2)-FIND(",",B2,FIND(",",B2)+1)-1)

Here’s an explanation of the formula so you can modify it according to your needs:

• FIND(",",A1,FIND(",",A1)+1) locates the position of the second comma.
• LEN(A1) calculates the total length of the address string.
• Subtracting these positions extracts everything after the second comma.

Find above an example of using the formula in E2 to extract the state name and ZIP code.

The formula can be copied to the rest of the column using the fill handle.

Excel will find and extract the ZIP code and state name effortlessly.

Separate Address Using Regular Expressions

If you’re using the latest Excel for the Microsoft 365 app, you can use the REGEXEXTRACT for more efficient separation of full addresses into street, city, and state names. Find below the formulas, steps, and address format you can use:

Separate Street Name

Suppose, your source dataset contains the address string in the following format:

123 Main St, Springfield, IL 62701 

Use this formula in the destination cell to separate the street name from the entire address:

=REGEXEXTRACT(B2,"^(.*?)(?=,)")

You only need to change the cell range reference B2 in the above formula.

Split the City Name

If you wish to extract the city name between the two commas in the address, use this formula instead:

=REGEXEXTRACT(B2, "(?<=,\s)([^,]+)(?=,)")

Simply replace B2 with a cell address according to your own dataset.

Separate the State & ZIP Code

Here’s the REGEXTRACT formula to separate the state & ZIP from an address:

=REGEXEXTRACT(B2, "\s([A-Z]{2}\s\d{5})") 

Make sure you change the cell range reference B2 to an appropriate reference according to your worksheet.

Separate Address Using Power Query

Power Query allows you to transform an external dataset into a clean structure before you import it into an Excel worksheet.

There are two ways to import data to the Power Query Editor. Firstly, click the Data tab in the Excel workbook and click the Get Data command.

You can now hover the cursor over the primary data source, like From File, Database, Azure, etc.

A context menu with more options will open from which you can choose the exact data source, like From Azure SQL Database if you’ve chosen From Azure earlier.

Now, follow the onscreen instructions to import the dataset.

Select a dataset already available in your worksheet, and click on the From Table/Range command in the Data tab.

Click OK on the Create Table dialog box to create a data connection between Power Query and the worksheet.

Once you see the source dataset in Power Query Editor, click on the column header to select the whole data column.

Now, click on the Split Column command in the Home tab and choose the By Delimiter option in the context menu.

The Split Column by Delimiter wizard will show up.

Click on the Select or enter delimiter drop-down menu and choose the specific delimiter used in the full address, like Comma.

Select Each occurrence of the delimiter in the Split at section.

Click OK to apply the changes to the source dataset.

Double-click on the column headers and rename the header text for clarity. You can use headers, like Street, City, and State & ZIP.

Now, click on the File menu and select Close & Load To from the context menu.

The Import Data dialog box will open. Select the Existing worksheet option and choose the destination cell in the active worksheet.

Click OK to export the transformed data to the active worksheet.

Congratulations! You’ve successfully separated addresses in Excel using Power Query.

📚 Read more: If you liked this Excel tutorial, you might want to check out these too:

• 8 Ways to Split Text by Delimiter in Excel
• 7 Ways to Extract the First or Last N Characters in Excel
• 9 Easy Ways To Combine Two Columns in Microsoft Excel

Conclusions

Now you know how to separate addresses in Excel using user interface commands, like Flash Fill, Text to Columns, Excel functions, and Power Query.

here, I’ve shown you how to use the newly included REGEXEXTRACT function to split addresses into columns.

Did you find this Microsoft Excel tutorial useful? Comment below to share your feedback!

• Using Text to Columns: Easily split addresses into separate columns (e.g., street, city, state, ZIP code) using Excel’s Text to Columns feature. Choose a delimiter like a comma or space for precise separation.
• Using Flash Fill: Excel’s Flash Fill automatically detects patterns and separates address components when you start typing an example. Perfect for quick and simple extractions.
• Using Formulas (LEFT, MID, RIGHT, FIND, LEN): Extract specific parts of an address using functions like LEFT(), MID(), RIGHT(), and FIND(). Ideal for addresses with consistent formatting.
• Using Power Query: Transform and split address data efficiently using Power Query, which allows more advanced customization and automation. Best for handling large datasets.
• Using VBA (Macro): Automate address separation with a VBA script, allowing batch processing of complex address structures and ensuring efficiency for repeated tasks.