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

Get FREE Advanced Excel Exercises with Solutions!

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:
`=IF(ISERROR(VALUE(LEFT(B5,1))),"",LEFT(B5,FIND(" ",B5)-1))`
• 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. 🔎 How Does the Formula Work?

• FIND(” “,B5)-1

This part of the formula looks for space in B5 and returns 8.

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

This function generates 711-2880.

• VALUE(LEFT(B5,1))

Next, the output of this part is 7.

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

The output is False in this case.

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

### 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:
`=IF(ISNUMBER(VALUE(LEFT(B5,1))),LEFT(B5,FIND(" ",B5)-1),"")`
• Now, press Enter to return the value.
• Next, use AutoFill to fill the rest.
• At last, you’ll get the desired output. 🔎 How Does the Formula Work?

• FIND(” “,B5)-1

This formula looks for space in B5 and returns 8.

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

This part generates 711-2880.

• VALUE(LEFT(B5,1))

The output of this part is 7.

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

The output is True in this case.

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

Eventually, 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

### 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:
`=LEFT(B5,FIND(" ",B5,1))`
• Press Enter.
• Lastly, use AutoFill to return the desired output. 🔎 How Does the Formula Work?

• FIND(” “,B5)-1

First, this part looks for space in B5. Then, it returns 8.

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

Finally, it generates 711-2880.

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

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , and premium Excel consultancy services for Excel and business users. Feel free to contact us with your Excel projects. Aung Shine

My name is Aung. I have my B.Sc. degree in EEE. From now on, I will be working with Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  