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.
Download Practice Workbook
Download the following workbook to practice by yourself.
6 Easy Ways to Separate Address Number from Street Name in Excel
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.
Read More: How to Separate Address in Excel with Comma (3 Easy Methods)
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 Effective Methods)
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.
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.
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 (2 Effective Ways)
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.
Read More: How to Format Addresses in Excel (4 Easy Methods)
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. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.
Related Articles
- How to Format Address Labels in Excel (3 Steps)
- Make Address Labels in Word from Excel (With Easy Steps)
- How to Format a Column for Email Addresses in Excel (2 Easy Ways)
- Formula to Create Email Address in Excel (2 Suitable Examples)
- How to Make an Address Book in Excel (An Ultimate Guide)
- Create Email Address with First Initial and Last Name Using Excel Formula