Written by Allen Wyatt (last updated November 15, 2024)
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
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 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:
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.
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!
If you have a column of data that contains color-coded cells, you may want a way to sum values in the column based on the ...
Discover MoreYou can easily use the COMBIN worksheet function to determine the number of combinations that can be made from a given ...
Discover MoreIn mathematics, the sum of a range of sequential integers, starting with 1, is known as a triangular number or Gaussian ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2024-11-19 15:57:03
@Hans Hallebeek
This may be too complicated. In Poland the addresses usually start with the abbreviation for the street type (ul. for street, or pl. for square, or al. for avenue; luckily there are much fewer options than in US or Canada). Then the name of the street/square/avenue follows (one or more words), followed by the house number, then another abbreviation "m." for apartment number, and finally the apartment number, which may have letters appended.
To handle this, you probably need to parse the text from the end backwards. The first number you find (closest to the end) with any following non numeric characters would be the apartment number, the next number with any additional characters following it would be the house number. Note that any digits in the street name would likely not interfere as they will be earlier in the text than the house number.
I would still split the address by Text-to-Columns with the space as the delimiter, then use a macro to process parts of the address one cell at a time starting from the rightmost cell, then recreate parts of the address as needed.
I don't have a need for such parsing, so I won't develop such a macro now, but if you do have such need, and would like help or more ideas on that, send me an e-mail (my address is un-hidden for this comment).
2024-11-19 15:11:40
Tomek
A screenshot for my earlier comment, hopefully with better resolution. (see Figure 1 below)
Figure 1. Example
2024-11-19 15:06:57
Tomek
RE: 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.**
It may not be too complicated if you follow steps below.
Assuming the addresses are in column C, and every address starts with a house number followed by a space. The house numbers can contain non-numeric characters, but cannot contain spaces. (see Figure 1 below)
1. Do Text-to-Columns with the space as the delimiter on the data in column C.
After that you will have only the house number in column C, followed by several cells with the rest of the
address, one word per cell.
2. In the column A (cell A2) enter =C2. This will get the house number.
3. In the column B (cell B2) enter the formula
=TRIM(CONCAT(D2:Q2 & " "))
Adjust the address in the formula D2:Q2 to cover the longest address.
Appending the space is needed to restore spaces between words. See NOTE.
4. Copy the formula from the cells A2 and B2 down for as many rows as needed
If you want to get rid of the helper columns, first Copy the cells in columns A and B and Paste-Values back in the same spot to get rid of the formulas, the delete the helper columns.
NOTE: the trim function removes unnecessary spaces at the end of the concatenated address.
NOTE 2: you must use CONCAT rather than CONCATENATE function; the latter may result in an array.
Figure 1. Example
2024-11-16 04:42:46
Hans Hallebeek
Great for addressed where the number is at the beginning, what about numbers like here in Europe where the housenumbers are at the end and sometimes even have an extension like 23 A or 1304-B ect etc
2024-11-15 05:25:34
jamies
Firstly, when extracting a string it is best to stop Excel storing it as a number,
use =""&TEXT(VALUE(..),"0")
indeed the ""& bit makes the results of formulas that get a null result show a blank cell rather than a 0
And
there are addresses that are a range of numbers "12-23"
And those with building names instead of a number
And maybe a residence number within the property address.
Or P.O. Box
Lots of ways not to have an easily identifiable "number "
And remember Excel just loves storing things like 60E as a number, and 2/3 as a floating point value 0.666666666666666
Even more so with the AI processing that Macrosnaff-U has built into the Excel App, so you have to keep stopping it from corrupting or interfering with your worksheet setup.
And - in VBA - remember that .VALUE has excel look to make the data a date, or currency - well change a number with lots of decimal places to a text string starting with a $ and only having 2 decimal places -
So use .VALUE2
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
Reading your suggestions, specifically about Text to Column I just thought of another option. Load the data into PowerQuery.
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.
Take a look at this article:
https://www.xelplus.com/excel-text-transformation-power-query/
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments