# Moving the House Number to Its Own Cell

Written by Allen Wyatt (last updated November 7, 2020)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365

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

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))
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 String
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.)

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

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

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

Selecting a Graphic that is Behind Text

Position a graphic so that it is "behind" your text, and it may seem like you can no longer select the graphic. Here's ...

Discover More

Making Sure Word Doesn't Capitalize Anything Automatically

Word, in an effort to be helpful, will often change the capitalization of the words you type. If you tire of Word's ...

Discover More

Combining workbooks that have cross-links to each other can offer some special challenges. This tip examines how you can ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

##### More ExcelTips (ribbon)

Teachers often grade on what is affectionately referred to as "the curve." The problem is, it can be a bit difficult to ...

Discover More

Converting from Relative to Absolute

Addresses used in a formula can be either relative or absolute. If you need to switch between the two types of ...

Discover More

Randomly Assigning Names to Items

If you need to randomly match up items in two lists, there are a variety of techniques you can use. Here are a couple of ...

Discover More
##### Subscribe

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

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}] (all 7 characters, in the sequence shown) 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 3 + 7?

2020-11-08 11:24:11

Ron S MVP

Dave B:
You could break it down into smaller steps.
Split column on first space
Split column on first space (again)
Merge the 2 columns into 1
delete the "helper" columns.

The question is, are all of the numbers in your DB this way or do you have to build in intelligence.

OK, new idea, still in QP:
Filter for N/S in first char,
split column on first space
remove filter
filter for E/W in first char
split column on first space
remove filter
this should leave "normal" numbers
filter for leading alph (not sure how ...)
Split column on first space

Do you have names like "1'st St", that would also cause a problem with above

remo

2020-11-07 14:36:12

David Bonin

Here's a stumper for you --
Our more-suburban addresses often use two numbers, such as:
S6730 W27934 Hickory Ridge Lane

2020-11-07 13:11:59

Ron S MVP

Use the PQ Text to Column feature which is called Split column. It has options to split once, or multiple times, on space or other delimiter, or data change from number to char, or char to number.
https://www.xelplus.com/excel-text-transformation-power-query/

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