remove street address from address

[althea]

I have in one cell street address, city, state zip and I need only the street address in a cell by itself. I have over 40,000 rows of data. Any ideas?
Thanks!!

 

[althea]

Me again, here is the example below. The problem is that there are different cities in the data with different lengths......
123 oak street asheville nc 28222

 

[Welshbird]

Althea - is there always a space? 'Text to Columns' might help you, but not if you have
123 high street new york
and then
123 upper west street new york.



Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[althea]

text to columns will help but won't give me complete street address for every row because the number of characters differ in each street address.Thanks!

 

[AnotherHiggins]

40,000 rows? Yikes.

First, I'd just point out that this is why data shouldn't be stored like this in the first place. If you aren't the one who decided to store it that way, I think you're allowed to smack whomever did.

As Fee pointed out, the problem is going to be cities with spaces in the name, such as New York and Mount Airy.


Here's an idea: does the address always end with a period? As in "Oak St[highlight].[/highlight]" ? If not, I don't see any way to get around looking through to correct at least some of the results of whatever formula you use.

You could take everything to the left of the third-from-last space, which would always strip off zip, state and at least part of the city name. But those two-name cities will, I think, have to be handled manually. We can help you do that if you'd like.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

Actually, another idea just struck me. You could look for any of a series of strings that might end the address:
Street, Drive, Lane, Way, Terrace, etc. then take everything to the left of those strings. It won't be pretty, but it could be done.

But how to proceed depends on the exact format of your data.


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Hi,

This is an absolute mess!

The first task will be to anaylze the data, in order to determine if three is some logic that can be applied, as has aready been suggested above.

I have a feeling that it will take ALLOT of tweeking. Not an easy task.

40,00 rows? It's a MESS!

Anaysis, analysis, analysis -- before you do a lick of code.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[althea]

Thanks guys, all great suggestions! What we're going to do for this one is get another file from the customer that has street addresses as a separate field.

 

[AnotherHiggins]

Ah! customer-provided data. I guess you shouldn't smack them after all.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

An EXCELLENT course of action!

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[LNBruno]

Ok, this is as ugly as home-made sin, but it seems to do the trick (just in case someone has a less than cooperative client):

Assuming Street Names are in column A starting in row 2:

FIRST ELEMENT (B2)
	=MID(A2,1,(FIND(" ",A2)))
SECOND ELEMENT (C2)
	=MID(A2,(LEN(B2)+1),(FIND(" ",RIGHT(A2,LEN(A2)-LEN(B2)))))
THIRD ELEMENT (D2)
	=MID(A2,(LEN(B2)+LEN(C2)+1),(FIND(" ",RIGHT(A2,LEN(A2)-(LEN(B2)+LEN(C2))))))
FOURTH ELEMENT (E2)
	=MID(A2,(LEN(B2)+LEN(C2)+LEN(D2)+1),(FIND(" ",RIGHT(A2,LEN(A2)-(LEN(B2)+LEN(C2)+LEN(D2))))))
FIFTH ELEMENT (F2)
	=MID(A2,(LEN(B2)+LEN(C2)+LEN(D2)+LEN(E2)+1),(FIND(" ",RIGHT(A2,LEN(A2)-(LEN(B2)+LEN(C2)+LEN(D2)+LEN(E2))))))
SIXTH ELEMENT (G2)
	=MID(A2,(LEN(B2)+LEN(C2)+LEN(D2)+LEN(E2)+LEN(F2)+1),(FIND(" ",RIGHT(A2,LEN(A2)-(LEN(B2)+LEN(C2)+LEN(D2)+LEN(E2)+LEN(F2))))))
FIXED SIXTH ELEMENT (H2)
	=RIGHT(A2,LEN(A2)-(LEN(B2)+LEN(C2)+LEN(D2)+LEN(E2)+LEN(F2)))

< M!ke >
I am not a hamster and life is not a wheel.

 

[AnotherHiggins]

M!ke:

I hate to be the one to bring this up, but what if one of the addresses is:
123 Oak View Drive Mount Airy nc 27030 ?

As was pointed out earlier, having spaces within some of the elements messes things up a ton.

Also, since zip codes are a set-length (assuming they always use the 5-digit version and never "zip+4") and state abbreviations are always 2 characters, you can call those elements out much easier. eg =right(A1,5)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[LNBruno]

Of course, you'd need to re-construct the bits you need for the street-only from the de-constructed values in the cell, but, hey!

Betcha I end up using this myself in the not too distant future....

Have a great weekend! :-D

< M!ke >
I am not a hamster and life is not a wheel.

 

[mintjulep]

I hate to be the one to bring this up, but what if one of the addresses is:
123 Oak View Drive Mount Airy nc 27030 ?

Or even worse

123 Oak View Drive Apt 3B Mount Airy nc 27030

Now, if you want to get fancy:

The zip is easy to parse out.

Look up the zip in the Post Office zip code database. This will give you the city and state.

What ever is left is the street address.

 

[AnotherHiggins]

ooooh! That's clever, mintjulep.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[fneily]

Not necessarily. When I address a certain address, I can either put Baltimore, Maryland 21236 OR Nottingham, Maryland 21236 and it gets to the same place. The Post Office Zip database will show only Nottingham, not Baltimore. So parsing on Nottingham would miss Baltimore.

 

[TomCologne]

Out of curiosity, what USPS ZIP db are you referring to, one of the USPS website lookup tabs, eg

http://zip4.usps.com/zip4/welcome.jsp?

A few years ago I had a similar problem with a combined address field. I ended up looking for "Street, Road, Way, etc...". As anotherhiggins mentioned, not pretty at all, but it generally worked for me.

Fortunately, there were only about 5,000 entries because this method would fail miserably on some street names, especially "Broadway"!

TomCologne

 

[mintjulep]

I wasn't referring to any specific DB. I just assumed that there are several out there.

 

[TomCologne]

Thanks anyway, mintjulep!

TomCologne

 

[LNBruno]

I hate to be the one to bring this up, but what if one of the addresses is: 123 Oak View Drive Mount Airy nc 27030 ?

Didn't mean to imply this was THE solution. It was simply to show how to split out the value from a single cell into its pieces, breaking at the spaces as a start; hence the follow up reference to "re-constructing."

As stated earlier,

an absolute mess

.

Sorry if I mislead....

< M!ke >
I am not a hamster and life is not a wheel.