Moving the House Number to Its Own Cell

by Allen Wyatt
(last updated January 10, 2015)

8

Tom works with a lot of lists of addresses. One thing that drives him crazy is when the street address is in a single cell. Tom needs the house number in one cell and the street in a different cell. So, if the address is "1234 Maple Glen Ave.," then he needs "1234" in one cell and "Maple Glen Ave." in another. He can copy the house number into a column placed before the street name but it's a long process to retype the numbers and then eliminate them from the street name. Tom wonders if there is a simpler way that would "split" the house number from the street name.

There are a couple of ways you can go about this task. Some might think that using the Text to Columns tool (on the Data tab of the ribbon) would give the desired results. Unfortunately, it is not the best way to go about separating out the house number. The reason is simple: If you use the tool by specifying a delimiter, the only one that makes any sense is the space. But if you do that, you end up with "1234 Maple Glen Ave." being spread across four columns instead of the desired two. You would then need to come up with a way to paste the street name back together.

The other way to potentially use the Text to Columns tool is with a fixed column width (instead of a delimiter). This approach will work only if all your addresses have the same number of digits in the house number. In most data sets, of course, this won't be the case—house numbers could be just about any number of digits, and they might even include non-numeric characters (such as "1234A", "1234-B", or "1234-36").

One approach that will work is to use formulas to pull apart the original addresses. Your formulas can key in on the first space in the address, returning either the part to the left of the space or the part to the right. Here is the way to pull out everything before the space, given an address in cell A1:

=LEFT(A1,FIND(" ",A1)-1)

If you are absolutely certain that the addresses won't contain any non-numeric characters, you could wrap the formula in the VALUE function so that you end up with the house number as a numeric value:

=VALUE(LEFT(A1,FIND(" ",A1)-1))

To grab the part of the address following the first space you can use this formula:

=MID(A1,FIND(" ",A1)+1,LEN(A1))

You could, if you prefer, devise a formula that references the house number you pulled out with the first formula. Assume, for example, that your house-number formula is in column B; you could place the following in column C:

=TRIM(SUBSTITUTE(A1,B1,))

With your formulas in columns B and C (and the addresses in column A), you could copy the formulas down for however many rows necessary. Then you can select that B:C range and use Paste Special to paste the values back into those cells. After doing this you can safely delete the original addresses in column A.

If you often have to do many addresses, you might be better served to use a macro to do the pulling apart. The following example macro works on whatever range you have selected when you run it. It inserts a column of blank cells to the left of the selection, stuffs those cells with the house number, and then adjusts the addresses so the house number is no longer included.

Sub SplitAddress()
    Dim c As Range
    Dim j As Integer
    Dim n As String
    Dim addr As String

    Selection.Insert Shift:=xlToRight
    Selection.Offset(0, 1).Select

    For Each c In Selection
        j = InStr(1, c, " ")
        n = Left(c, j)
        c.Offset(0, -1) = n
        addr = Trim(Right(c, Len(c) - j))
        c = addr
    Next
End Sub

If you prefer, you could create a user-defined function that returns just the house number:

Function GrabHouseNumber(Raw As String) As Text
    Dim x As Variant
    Dim House As Variant

    x = Split(Raw, " ")     'use space char to split elements into array
    House = x(0)   'first element of array
    If Left(House, 1) Like "#" Then
        'First char is numeric digit
        GrabHouseNumber = House  'set return value as house number
    Else
        GrabHouseNumber = ""  'First char is text, so not a house number
    End If
End Function

An advantage to using the user-defined function is that it checks to see if the first part of the original address actually starts with a number or not. If it doesn't, then it assumes that the address doesn't start with a house number. (The entire house number doesn't need to be numeric; it just needs to start with a digit.)

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13350) applies to Microsoft Excel 2007, 2010, and 2013.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Establishing a FLOOR and CEILING

Excel includes a surprising number of functions you can use to round your data. Two such functions are FLOOR and CEILING, ...

Discover More

Understanding the Clipboard

You can use the Clipboard to move information around in Word. You actually have access to two different clipboards in Word, ...

Discover More

Excel 2013 Serious Sorting (Table of Contents)

Sorting data means that you organize it in whatever order you desire. Excel's sorting feature can be used in a variety of ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Cell Address of a Maximum Value

Finding the maximum value in a range of cells is easy; finding the address of the cell containing that value is a different ...

Discover More

Checking for Proper Entry of Array Formulas

Excel allows you to enter two different types of formulas in a cell: A regular formula or an array formula. If you need to ...

Discover More

How Operators are Evaluated

Operators are used in formulas to instruct Excel what to do to arrive at a result. Not all operators are evaluated in the ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is seven minus 2?

2016-11-11 14:35:40

Lis

@Micky--

I need to find you and give you a 20 minute bear hug.

Thank you!!!!!


2016-11-10 08:58:52

Michael (Micky) Avidan

@Lis,
Try to change the decleration line to:
--------------------------
Function GrabHouseNumber(Raw As String) As String
--------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2017)
ISRAEL


2016-11-10 08:00:16

Willy Vanhaelen

@lis

You must copy the Function code in a module:
- press and hold down [Alt]
- press [F11] and im

That's the place to be.


2016-11-09 21:23:01

lis

How do you apply the GrabHouseNumber function? I'm using Max Excel 2011 and cannot get the user defined function to work.


2016-10-06 08:59:20

Jason

Doug-what version of Excel are you using?

If 2013+, I would suggest using Flash Fill. In the immediate column next to the address column, simply type only the house number for the first couple of cells. Then use the fill handle to "copy" the house number down the entire column. Then, using the formatting box that appears after you've copied, select Flash Fill. It should recognize the pattern you started and copy the data accordingly.

If prior version, it is a little more clunky. Using each of these in separate columns, I've used a combination of FIND (to find the first blank position); LEFT (to "pull out" the house number); MID (to "pull out" the street name); and CONCATENATE (to combine fields in the order you want).

Email me if you get stuck.


2016-10-05 17:10:31

Doug Polk

I am trying to match addresses in two columns in order to find those that are not identical. Problem is that in one column the house number is first and in the other column the house number follows the street name. How can I get both to have identical addresses if both are the same address.


2015-01-13 11:40:46

Tom

Thanks for all the tips regarding separation of street numbers from street names. I'm not sure that I "get it" but I will work on it. I like to say that I'm "half-a-geek regarding computers (and programs) and know just enough to be dangerous." I am truly appreciative for the help.


2015-01-10 11:57:35

Jason Swartzlander

"Flash Fill" is the most efficient way to do this. It is available in Excel 2013.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.