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.


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.

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

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

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

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

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

  • Additionally, choose the desired destination and press Finish.

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

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

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


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.

Split Address Number with Excel VBA

  • 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

Split Address Number with Excel VBA

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

Split Address Number with Excel VBA

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

Split Address Number with Excel VBA

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

Split Address Number with Excel VBA

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.

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

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

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

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

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

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

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

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

 

Aung

Aung

I'm Aung. Recently I've earned my B.Sc. Degree in Electrical and Electronic Engineering. From now on, I will be working in 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

Leave a reply

ExcelDemy
Logo