Data scrubbing a list of street addresses - how to trim off street number?

[broken keyboard]

Hi all,

Hoping to find some help on this one! I have a list of thousands of addresses containing numbers and street names, and I'm trying to "shave off" the number (and sometimes, unit name - e.g. 15/A or 64C) from this list in order to isolate the street name. Unfortunately there are no clear delimiters to work with that I can see. Here's a sample of some of the data below:

[TABLE="width: 233"]
<tbody>[TR]
[TD="width: 233"]187 Puriri Street
[TABLE="width: 233"]
<tbody>[TR]
[TD="width: 233"]221A-D Puriri Street
[TABLE="width: 233"]
<tbody>[TR]
[TD="width: 233"]48A&B Great North Road
[TABLE="width: 233"]
<tbody>[TR]
[TD="width: 233"]76/A Treadwell Street[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 233"]
<tbody>[TR]
[TD="width: 233"]60,62 Great North Road
[TABLE="width: 233"]
<tbody>[TR]
[TD="width: 233"]63a,b,c Polson Street
[TABLE="width: 233"]
<tbody>[TR]
[TD="width: 233"]4 Seafront Road
[TABLE="width: 233"]
<tbody>[TR]
[TD="width: 233"]336 Wicksteed Street
[TABLE="width: 233"]
<tbody>[TR]
[TD="width: 233"]6, 1 -20 Kawakawa Street
[TABLE="width: 233"]
<tbody>[TR]
[TD="width: 233"]41a 1-3 Rimu Street
[TABLE="width: 233"]
<tbody>[TR]
[TD="width: 233"]68,68C,B,A Nixon Street
[TABLE="width: 176"]
<tbody>[TR]
[TD="width: 176"]75 - 85 Ridgway Street
[TABLE="width: 176"]
<tbody>[TR]
[TD="width: 176"]40 D'Arcy Road[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Every address always starts with a number without fail. Any help would be much appreciated!

PS: I'm using Excel 2010 on Windows 8.

 

[broken keyboard]

Hi Peter, Brian,

Thanks for your huge efforts with helping me refine macros to cleanse this address data. I used both of your most recent macros you have posted to help get to a point where really everything is looking good - with the only issues remaining being those with the data itself (for example, one address provided was just called "Street" - not the most helpful address!). Thanks again - much appreciated!

 

[Peter_SSs]

Hi Peter, Brian,

Thanks for your huge efforts with helping me refine macros to cleanse this address data. I used both of your most recent macros you have posted to help get to a point where really everything is looking good

Good to hear.

one address provided was just called "Street" - not the most helpful address!

Yes, I don't think that we can help with that one!

 

[BrianJN1]

Hi Peter, Brian,

Thanks for your huge efforts with helping me refine macros to cleanse this address data. I used both of your most recent macros you have posted to help get to a point where really everything is looking good - with the only issues remaining being those with the data itself (for example, one address provided was just called "Street" - not the most helpful address!). Thanks again - much appreciated!

Glad to have been part of your success; 60,000+ addresses would be massive to do manually