# 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:
`=IF(ISERROR(VALUE(LEFT(B5,1))),"",LEFT(B5,FIND(" ",B5)-1))`
• Press Enter to return the Address Number.

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

How Does the Formula Work?

• FIND(” “,B5)-1

Looks for space in B5 and returns 8.

• LEFT(B5,FIND(” “,B5)-1)

Generates 711-2880.

• VALUE(LEFT(B5,1))

The output is 7.

• ISERROR(VALUE(LEFT(B5,1)))

The output is False.

• IF(ISERROR(VALUE(LEFT(B5,1))),””,LEFT(B5,FIND(” “,B5)-1))

Lastly, the IF function returns the outcome of LEFT(B5,FIND(” “,B5)-1) i.e 711-2880 as the condition is False.

### 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:
`=IF(ISNUMBER(VALUE(LEFT(B5,1))),LEFT(B5,FIND(" ",B5)-1),"")`
• Press Enter to return the value.
• Use AutoFill to fill the rest.

The desired output is returned.

How Does the Formula Work?

• FIND(” “,B5)-1

This formula looks for space in B5 and returns 8.

• LEFT(B5,FIND(” “,B5)-1)

Generates 711-2880.

• VALUE(LEFT(B5,1))

The output is 7.

• ISNUMBER (VALUE(LEFT(B5,1)))

The output is True.

• IF(ISNUMBER(VALUE(LEFT(B5,1))),LEFT(B5,FIND(” “,B5)-1),””)

The IF function returns the outcome of LEFT(B5,FIND(” “,B5)-1) i.e 711-2880 as the condition is True.

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:
`=LEFT(B5,FIND(" ",B5,1))`
• Press Enter.
• Use AutoFill to return the desired output.

How Does the Formula Work?

• FIND(” “,B5)-1

Looks for space in B5 and returns 8.

• LEFT(B5,FIND(” “,B5)-1)

Generates 711-2880.

### 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.

### 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.

### 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.

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Aung Shine

Aung Shine completed his bachelor’s in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. It has been almost 2 years since he joined SOFTEKO and actively working on the ExcelDemy project. Currently he works as a Team Leader where he guides his team members to create technical content. He has published 150+ articles and reviewed 50+ articles. He has also solved various user problems before. He has interests in Data Analysis, Power Query, Advanced Excel,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF