4 replies to this topic

[BZero]

I would like to find a way of accomplishing breaking up streets addresses portrayed in the example below where ONE FIELD is Street #, another is street prefix, street name, type and suffix without doing it manually.

I am talking about thousands upon thousands of streets addresses here.

The issue is street addresses like this

5585 N John Barker Rd
10327 S Main Rd
501 Westside Villa Dr
150 S Hwy 88
9999 Grandview Dr E

Now one cannot just do a space-delimited as the above illustrate. Westside Villa while is a street name would be split into Westside for prefix and Villa for name.

Even if you did a fixed width on just one at a time one would still have issues obviously.

Whether any such thing is possible in ArcGIS, excel, whatever is unknown.

[natcase]

I would like to find a way of accomplishing breaking up streets addresses portrayed in the example below where ONE FIELD is Street #, another is street prefix, street name, type and suffix without doing it manually.

I am talking about thousands upon thousands of streets addresses here.

The issue is street addresses like this

5585 N John Barker Rd
10327 S Main Rd
501 Westside Villa Dr
150 S Hwy 88
9999 Grandview Dr E

Now one cannot just do a space-delimited as the above illustrate. Westside Villa while is a street name would be split into Westside for prefix and Villa for name.

Even if you did a fixed width on just one at a time one would still have issues obviously.

Whether any such thing is possible in ArcGIS, excel, whatever is unknown.

Seems to me like a complex but not undoable formula. I'd do it in a database app just because that's what I'm use to working in, but Excel would presumably do it too. Preferably something that recognizes "words" as objects.

For street number, it's leftword.

For prefix it's if(middleword([string],2)="E" or (middleword([string],2)=W... etc, then (middleword([string],2), else ""
For suffix its the same, using rightword.

If you don't have "word" functions, you have to construct more cumbersome statements/formulae with spaces and character counts.

The tricky part is going to be the street name and type. If these can be one item, you can just substitute streetnumber&" " with "", and do the same with the other fields you've defined. If you need to separate "Main" and "St", you may need to create a list of possible "type" options, and find for each of them.

Hope this all makes sense.

[BZero]

I would like to find a way of accomplishing breaking up streets addresses portrayed in the example below where ONE FIELD is Street #, another is street prefix, street name, type and suffix without doing it manually.

I am talking about thousands upon thousands of streets addresses here.

The issue is street addresses like this

5585 N John Barker Rd
10327 S Main Rd
501 Westside Villa Dr
150 S Hwy 88
9999 Grandview Dr E

Now one cannot just do a space-delimited as the above illustrate. Westside Villa while is a street name would be split into Westside for prefix and Villa for name.

Even if you did a fixed width on just one at a time one would still have issues obviously.

Whether any such thing is possible in ArcGIS, excel, whatever is unknown.

Seems to me like a complex but not undoable formula. I'd do it in a database app just because that's what I'm use to working in, but Excel would presumably do it too. Preferably something that recognizes "words" as objects.

For street number, it's leftword.

For prefix it's if(middleword([string],2)="E" or (middleword([string],2)=W... etc, then (middleword([string],2), else ""
For suffix its the same, using rightword.

If you don't have "word" functions, you have to construct more cumbersome statements/formulae with spaces and character counts.

The tricky part is going to be the street name and type. If these can be one item, you can just substitute streetnumber&" " with "", and do the same with the other fields you've defined. If you need to separate "Main" and "St", you may need to create a list of possible "type" options, and find for each of them.

Hope this all makes sense.

yeah it makes sense to a degree. I just suck at complex formulas, especially in excel. If anyone has any other thoughts as well, that would be great.

[Nick H]

I would like to find a way of accomplishing breaking up streets addresses portrayed in the example below where ONE FIELD is Street #, another is street prefix, street name, type and suffix without doing it manually...

I'm not sure that I understand the problem (but this is probably me). From what you've written it appears that you want to split off the house numbers as one field (e.g '5585') and the other data into a second field (e.g. 'N John Barker Rd'). It would be quite easy to do this, but I'm not sure if this is what you want.

Regards, N.

[jbl]

Regular Expressions would be the way that I would do this. The complexity would depend on whether all of the addresses were formed the same way. In your example they are not. Do-able but not simple.

5585 N John Barker Rd
10327 S Main Rd
501 Westside Villa Dr
150 S Hwy 88
9999 Grandview Dr E

Of these, some have direction prefixes, some not, some have street suffixes, some not.
You might first search the end of the line for sufixes (N E S W NE NW SE SW, etc...) and tag the ones that you find with a unique symbol, ie... NE becomes *NE*

Then Search the beginning of the line for ^\d+\s+(\W{1,2})\s+ ( start at the beginning of the line, any number of digits followed by one or more spaces, followed by one or 2 capitol letters (capture these for tagging, these would be a prefix), followed by one or more spaces. Just hope that there are no street names of one or two capitol letters that have no street prefix. 1234 AA Street SW for example, the regex would think AA was a prefix and not the name.

It would definately take some playing around with, but chop it up into smaller problems, not just one big problem.