How to Separate Address Number from Street Name in Excel (6 Ways)

We store important information in an Excel workbook. Many companies choose Excel to keep the Customers’ Addresses. Because with Excel, we can carry out numerous operations as per our requirements. Sometimes, we may need to collect the Address Number only instead of the whole address or Street Name. In this article, we’ll show you the effective yet simple methods to Separate the Address Number from Street Name in Excel.

How to Separate Address Number from Street Name in Excel: 6 Easy Ways

To illustrate, we’ll use a sample dataset as an example. For instance, in the following dataset, we have some Addresses in column B. The address consists of Street Number and Name, City, and State. Here, we’ll separate the Street Number from the Street Name in Excel.

1. Combine IF, ISERROR, VALUE, LEFT & FIND Functions to Separate Address Number from Street Name

The IF function tests a condition. It returns a value if the condition is satisfied. And if it’s False, the function returns another specified value. The ISERROR function verifies whether a value is an error or not. The VALUE function converts a text string to a number. The LEFT function generates a mentioned number of characters from the starting of a text string. The FIND function looks for a particular character in a text string and returns the position of that character. Therefore, follow the steps below to combine these functions to Separate the Address Number from Street Name.

STEPS:

• First, select cell C5.
• Then, type the formula:

• Subsequently, press Enter. Thus, it’ll return the Address Number.

• Finally, use the AutoFill tool to complete the rest.
• In this way, you can split the address number.

Read More: How to Separate Address in Excel with Comma

2. Unite IF, ISNUMBER, VALUE, LEFT & FIND Functions for Splitting Address Number in Excel

This method is almost the same as the previous one. In this method, we’ll insert the ISNUMBER function instead of ISERROR. This function tests whether the argument is a number or not. Hence, returns True or False. So, learn the following steps to unite these functions for performing the task.

STEPS:

• Firstly, choose cell C5 to type the formula:

• Now, press Enter to return the value.
• Next, use AutoFill to fill the rest.
• At last, you’ll get the desired output.

Read More: How to Separate City and State in Excel

3. Separate Address Number from Street Name with Excel LEFT & FIND Functions

The formula in this method is very simple compared to the earlier ones. Here, we’ll just use the LEFT & FIND functions. Hence, follow the process below.

STEPS:

• First of all, in cell C5, type the formula:

• Press Enter.
• Lastly, use AutoFill to return the desired output.

Read More: How to Separate City State and Zip from Address Using Excel Formula

4. Apply Text to Columns Feature to Get Address Number from Street Name

Moreover, we can execute the Text to Columns feature for splitting Address Number from Street Name. To perform the task, learn the following process.

STEPS:

• Select the range B5:B8 at first.
• Then, select Data ➤ Data Tools ➤ Text to Columns.

• As a result, a dialog box will pop out.
• Choose Delimited and press Next.

• Afterward, check the box for Space.
• Press Next.

• Additionally, choose the desired destination and press Finish.

• Hence, you’ll get to see the Address Number as it’s shown in the below figure.

Read More: How to Separate Address in Excel

5. Split Address Number with Excel VBA

However, we can get this job done through the Excel VBA too. Therefore, see the following steps to carry out the operation.

STEPS:

• In the beginning, go to the Developer tab and select Visual Basic.

• In the pop-out VBA window, select Insert ➤ Module.
• Consequently, the Module dialog box will appear.
• Copy the below code and paste it into the box.

Sub SplitAddNum()
    Dim st As String
    Dim p As Integer
    Dim i As Integer
    For Each cell In Selection
        st = cell.Value
        p = InStr(st, " ")
        If p > 0 Then
            i = Val(Left(st, p))
            If i > 0 Then
                cell.Offset(0, 1).Value = i
                st = Trim(Mid(st, p, Len(st)))
            End If
        End If
        cell.Offset(0, 2).Value = st
    Next
End Sub

• Save the file and close the VBA window.
• Now, choose the range B5:B8.
• Next, click Developer ➤ Macros.

• As a result, the Macro dialog box will emerge.
• Select SplitAddNum and press Run.

• Thus, it’ll return the address in separate columns.

Read More: How to Split Inconsistent Address in Excel

6. Use Flash Fill Feature to Separate Address Number from Street Name in Excel

Furthermore, the Excel Flash Fill feature can Separate the Address Number from the Street Name very easily. So, go through the steps below to know how.

STEPS:

• Firstly, input the first row like it’s demonstrated in the following picture.

• Then, click cell C6 and go to Data ➤ Data Tools ➤ Flash Fill.
• You can also use the Ctrl and E keys together.

• As a result, it’ll return our expected outcome.

• Similarly, you can get the other fields repeating the above steps.

Download Practice Workbook

Download the following workbook to practice by yourself.

Conclusion

Henceforth, you will be able to Separate the Address Number from Street Name in Excel following the above-described methods. Keep using them and let us know if you have more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.

Related Articles

• How to Separate Address in Excel Using Formula
• How to Separate City and State without Commas in Excel

<< Go Back to Address Format | Text Formatting | Learn Excel