5 Ways To Split Address In Excel

Dealing with addresses in Excel can be a real challenge, especially when they are not properly formatted. Sometimes, you may receive a list of addresses with the street number, street name, city, state, and zip code all combined into one cell. This can make it difficult to work with the data, as you may need to use specific parts of the address for mapping, mail merges, or other applications.

In this article, we will explore five ways to split an address in Excel. Whether you are dealing with a small list of addresses or a large database, these methods will help you to extract the relevant information and make your data more usable.

Method 1: Using Text to Columns

One of the easiest ways to split an address in Excel is to use the Text to Columns feature. This feature allows you to split text into separate columns based on a specific delimiter, such as a space or a comma.

To use Text to Columns, follow these steps:

1. Select the cell or range of cells that contains the addresses.
2. Go to the Data tab in the ribbon.
3. Click on the Text to Columns button in the Data Tools group.
4. In the Convert Text to Columns dialog box, select the delimiter that separates the different parts of the address. For example, if the address is separated by spaces, select the Space option.
5. Click on the Next button.
6. In the next step, you can specify the data format for each column. For example, you can select the Text format for the street number and the street name.
7. Click on the Finish button.

Excel will then split the address into separate columns based on the delimiter you specified.

Method 2: Using the Flash Fill Feature

Another way to split an address in Excel is to use the Flash Fill feature. This feature allows you to extract specific parts of the address and fill them into separate columns.

To use Flash Fill, follow these steps:

1. Select the cell or range of cells that contains the addresses.
2. Go to the Data tab in the ribbon.
3. Click on the Flash Fill button in the Data Tools group.
4. In the Flash Fill dialog box, select the column that contains the addresses.
5. In the Extract section, select the part of the address that you want to extract. For example, you can select the Street Number option to extract the street number.
6. Click on the Fill button.

Excel will then extract the specified part of the address and fill it into a new column.

Method 3: Using Formulas

You can also use formulas to split an address in Excel. One way to do this is to use the LEFT and RIGHT functions in combination with the FIND function.

For example, suppose you want to extract the street number from an address. You can use the following formula:

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

This formula extracts the characters to the left of the first space in the address, which should be the street number.

Similarly, you can use the following formula to extract the street name:

=RIGHT(A1,LEN(A1)-FIND(" ",A1))

This formula extracts the characters to the right of the first space in the address, which should be the street name.

You can use similar formulas to extract other parts of the address, such as the city, state, and zip code.

Method 4: Using Power Query

If you are using Excel 2013 or later, you can use Power Query to split an address. Power Query is a powerful data manipulation tool that allows you to extract and transform data from a variety of sources.

To use Power Query to split an address, follow these steps:

1. Select the cell or range of cells that contains the addresses.
2. Go to the Data tab in the ribbon.
3. Click on the New Query button in the Get & Transform Data group.
4. In the Query Editor, select the column that contains the addresses.
5. Click on the Split Column button in the Home tab.
6. In the Split Column dialog box, select the delimiter that separates the different parts of the address.
7. Click on the OK button.

Power Query will then split the address into separate columns based on the delimiter you specified.

Method 5: Using VBA Macros

Finally, you can use VBA macros to split an address in Excel. VBA macros are a powerful tool that allows you to automate repetitive tasks and perform complex data manipulation.

To use VBA macros to split an address, follow these steps:

1. Select the cell or range of cells that contains the addresses.
2. Press Alt + F11 to open the Visual Basic Editor.
3. In the Visual Basic Editor, click on Insert > Module to insert a new module.
4. Paste the following code into the module:

Sub SplitAddress()
    Dim cell As Range
    For Each cell In Selection
        Dim address As String
        address = cell.Value
        Dim streetNumber As String
        streetNumber = Left(address, InStr(address, " ") - 1)
        Dim streetName As String
        streetName = Mid(address, InStr(address, " ") + 1)
        cell.Offset(0, 1).Value = streetNumber
        cell.Offset(0, 2).Value = streetName
    Next cell
End Sub

5. Click on Run > Run Sub/UserForm to run the macro.

This macro will split the address into separate columns based on the space delimiter.

Address Splitting Image Gallery

We hope this article has helped you to learn how to split an address in Excel. Whether you are using the Text to Columns feature, Flash Fill, formulas, Power Query, or VBA macros, there are many ways to extract the relevant information from an address. Do you have any experience with address splitting in Excel? Share your thoughts and tips in the comments below!