Splitting of address field

[shorty]

I imported 60,000 addresses from another program of ours. Only problem I am having is that I need to split the house number and street names apart. I have a field called address(the imported one),made an address2 field,calculation field using leftwords(address,1).Worked great. Made an address3 field using the rightwords(address,2). Not so good. Address sometimes includes house number or not all the street name, based on the number of words in address. What am I missing here? Any help would be appreciated.

[BobWeaver]

I think you are in trouble. There are so many different combinations of words and numbers in street addresses that no calculation is likely to separate them properly. Look at the following perfectly valid street addresses:

123 Pine Street

123 Oak Avenue South

123 Martin Luther King Blvd.

123 West Fifty First Street

123-7th St. East, Apt. 16

31-37, Des Voeux Road, Central

4-1, Meguro 1-Chome, Meguro-ku

I tried to filter through a mess like this once. It requires a lot of manual work.

[LiveOak]

There may not be a universal answer given all the ways data can be entered. You can try:

address3 (calculation, text) =

Right(address, Length(address) - Position(address, " ", 1, 1))

This still won't help with addresses such as:

123 B Main Street (123B Main Street would be OK, which would become "B Baker Street". An address such as 123 B Street would be OK. Pretty hard stuff for a computer.

-bd

[BobWeaver]

This a situation where I like to break a big problem into several smaller problems. Split the list into 600 smaller lists, buy 7200 bottles of beer, and invite over 600 of your best friends. Many hands make light work. The cost of the beer is a legitimate business expense.

Seriously, I assume you want the street name separate for some special reason. Are you trying to sort by area or something? Maybe you can still accomplish what you are trying to do in a different way.

[BobWeaver]

Actually, just another thought. Sometimes making a list of key words to search for can clean up an ugly mess. For example you could make up a list of all the possible words and abbreviations for "street" such as:

street

St

Avenue

ave

boulevard

blvd

road

rd

drive

way

etc.

Then you can search for one of these and then move backwards until you hit a number and take everything in between. You can also make a list of words to delete like

North

East

West

South

Suite

Ste

Apt

Apartment

etc.

Sometimes you have to clean these things up in stages.

[shorty]

Thanks for the reply. I was trying to split them apart for sorting purposes. I am designing a circulation program for a free paper and they absolutely want it to sort by street name then house number on any given route. But I feel the same way we may have to do more manual work to the imported records first. Thanks.

[shorty]

To: Live Oak

Thank you very much. Your calculation worked beautifully. I now don't have to listen to the phone calls on who doesn't receive a paper. Worked like a dream.

[BobWeaver]

So, I guess you probably have a lot of beer left over then. If you need any help with that...

[LiveOak]

Works for me... -bd