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

Suppose we have the following dataset. Let’s separate the Street Numbers in the Address column into column D.

Method 1 – Combining IF, ISERROR, VALUE, LEFT & FIND Functions

The IF function tests a condition, returns a value if the condition is satisfied, and another if it isn’t. 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 returns a specified number of characters from the start of a text string. The FIND function looks for a particular character in a text string and returns the position of that character.

Steps:

• Select cell C5.
• Enter the following formula:

• Press Enter to return the Address Number.

• Use the AutoFill tool to complete the rest of the column.

Read More: How to Separate Address in Excel with Comma

Method 2 – Combining IF, ISNUMBER, VALUE, LEFT & FIND Functions

This method is almost the same as the previous one, except we’ll insert the ISNUMBER function instead of ISERROR. This function tests whether the argument is a number or not and returns True or False.

Steps:

• In cell C5 enter this formula:

• Press Enter to return the value.
• Use AutoFill to fill the rest.

The desired output is returned.

Read More: How to Separate City and State in Excel

Method 3 – Using LEFT & FIND Functions

The formula in this method is very simple compared to the earlier ones.

Steps:

• In cell C5, enter the formula:

• Press Enter.
• Use AutoFill to return the desired output.

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

Method 4 – Using Text to Columns Feature

Steps:

• Select the range B5:B8.
• Select Data ➤ Data Tools ➤ Text to Columns.

A dialog box will pop out.

• Choose Delimited and click Next.

• Check the box for Space.
• Press Next.

• Select the desired destination and press Finish.

The Address Numbers will be extracted as below.

Read More: How to Separate Address in Excel

Method 5 – Using Excel VBA Code

Steps:

• Go to the Developer tab and select Visual Basic.

• In the pop-out VBA window, select Insert ➤ Module.

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.
• Choose the range B5:B8.
• Click Developer ➤ Macros.

The Macro dialog box will appear.

• Select SplitAddNum and press Run.

The macro will return the address number and the rest of the address in separate columns.

Read More: How to Split Inconsistent Address in Excel

Method 6 – Using Flash Fill Feature

Steps:

• Input the first row like in the following picture.

• Select cell C6 and go to Data ➤ Data Tools ➤ Flash Fill.
• Alternatively, press the Ctrl and E keys together.

Our expected outcome is returned.

• Return the other fields by repeating the above steps.

Download Practice Workbook

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