Split a string based on 2 different delimiters

[ChrisBat via AccessMonster.com]

Hi,

I have an A2K database with links to Excel spreadsheets. When I import the
data, I would like to split the address field up into Street Number, Street
Name, and Miscellaneous. The problem I'm running into is that the data I'm
getting has numerous formats, looking like this:

123,,main st,,Somewhere ONT
444 Sesame St, Somewhere ONT
bsmt 333 Any Street, Nowhere ONT
Apt 3 336 any street, nowhere,ONT
*456 Sesame Street Somewhere ONT
*1240,,Lower Any,Dr, AAG76GGYD

What i'd like to do is pull the data apart, and put it into a table where the
data looks like:
123 | Main St | Somewhere ONT
444 | Sesame St |Somewhere ONT
333 | Any St |Nowhere ONT bsmt
336 | Any St | Nowhere ONT APt 3
456 | Sesame St | Somewhere ONT
1240 | Lower Any Dr | AAG76GGYD

Any suggestions would be much appreciated. I've been staring at this for at
least a day and a half, and have poked around on the net (the closest I got
was from one of the posts here which included the formula LEFT([_____],
INSTRREV([______]," ",LEN([_________])-7)) but that doesn't quite give me
what I need, and doesn't take into account the ,, or the spaces.....
Thanks in advance,
Chris

 

[Tom Ellison]

Dear Chris:

When you have "user entered data" like this, which has no consistent
structure, the severity of the work needed to put things back together is
limited only by the imagination of the people who chreated your nightmare.

It seems, given your very small sample of the perversion you have found so
far, that there something to do first. You need to isolate the street
address portion. It looks like the key is to isolate the word that tells
what kind of street, boulevard, road, avenue, or whatever similar term may
have been used. I recommend you use a table with two columns, like this:

St St
Street St
Road Rd
Rd Rd
Avenue Av
Ave Av

you will need a lot more that that, but you can fill in more as you discover
them.

The algorithm could them back up from that point to where it finds a series
of digits, the address number. This would form the first 2 parts of your
end product. Taking the rest of the string (after "street" and appending
anything left over at the beginning would finish this.

Whether this is exactly what you want may have to be determined after it is
working. It is one thing to design something on a small sample, another to
see what it does to a large amount of other information.

You will need good VBA coding skills to do this. I recommend reading the
above table of "kind of street" words into an array as a first step in the
process. This should not be done for every call to the process, but only
once for each time the application is run. A persistent flag will
accomplish this.

Please let me know if this helped, and if I can be of any other assistance.

Tom Ellison

Hi,

I have an A2K database with links to Excel spreadsheets. When I import
the
data, I would like to split the address field up into Street Number,
Street
Name, and Miscellaneous. The problem I'm running into is that the data
I'm
getting has numerous formats, looking like this:

123,,main st,,Somewhere ONT
444 Sesame St, Somewhere ONT
bsmt 333 Any Street, Nowhere ONT
Apt 3 336 any street, nowhere,ONT
*456 Sesame Street Somewhere ONT
*1240,,Lower Any,Dr, AAG76GGYD

What i'd like to do is pull the data apart, and put it into a table where
the
data looks like:
123 | Main St | Somewhere ONT
444 | Sesame St |Somewhere ONT
333 | Any St |Nowhere ONT bsmt
336 | Any St | Nowhere ONT APt 3
456 | Sesame St | Somewhere ONT
1240 | Lower Any Dr | AAG76GGYD

Any suggestions would be much appreciated. I've been staring at this for
at
least a day and a half, and have poked around on the net (the closest I
got
was from one of the posts here which included the formula LEFT([_____],
INSTRREV([______]," ",LEN([_________])-7)) but that doesn't quite give me
what I need, and doesn't take into account the ,, or the spaces.....
Thanks in advance,
Chris

 

[John Nurick]

Hi Chris,

As Tom says, this can be somewhere between very difficult and
impossible. I usually approach this sort of problem by using regular
expressions; it's not easy because the problem's not easy, but it saves
writing an awful lot of VBA code. If you search the newsgroups (e.g.
groups.google.com) for#
nurick "horse hockey" "small improvement"
you'll find an example of a procedure that uses a regular expression to
parse an address into its elements.

Hi,

I have an A2K database with links to Excel spreadsheets. When I import the
data, I would like to split the address field up into Street Number, Street
Name, and Miscellaneous. The problem I'm running into is that the data I'm
getting has numerous formats, looking like this:

123,,main st,,Somewhere ONT
444 Sesame St, Somewhere ONT
bsmt 333 Any Street, Nowhere ONT
Apt 3 336 any street, nowhere,ONT
*456 Sesame Street Somewhere ONT
*1240,,Lower Any,Dr, AAG76GGYD

What i'd like to do is pull the data apart, and put it into a table where the
data looks like:
123 | Main St | Somewhere ONT
444 | Sesame St |Somewhere ONT
333 | Any St |Nowhere ONT bsmt
336 | Any St | Nowhere ONT APt 3
456 | Sesame St | Somewhere ONT
1240 | Lower Any Dr | AAG76GGYD

Any suggestions would be much appreciated. I've been staring at this for at
least a day and a half, and have poked around on the net (the closest I got
was from one of the posts here which included the formula LEFT([_____],
INSTRREV([______]," ",LEN([_________])-7)) but that doesn't quite give me
what I need, and doesn't take into account the ,, or the spaces.....
Thanks in advance,
Chris

 

[ChrisBat via AccessMonster.com]

Thank you both John and Tom. I'm not sure how much more of this I'm going to
put my self through - this data manipulation was for a database that I was
hoping to link to Streets and Trips, but now I'm getting into Excel
automation through Access, and frankly this is not the most critical part of
the database. Although flashy and impressive, clean data is more important
for me right now than where the data is in the Province. Oh well, i'll put
it on my list of "Stuff I wanna learn"
Thank you again very much for your help.
See ya later!
Chris

Hi Chris,

As Tom says, this can be somewhere between very difficult and
impossible. I usually approach this sort of problem by using regular
expressions; it's not easy because the problem's not easy, but it saves
writing an awful lot of VBA code. If you search the newsgroups (e.g.
groups.google.com) for#
nurick "horse hockey" "small improvement"
you'll find an example of a procedure that uses a regular expression to
parse an address into its elements.

Hi,

[quoted text clipped - 26 lines]

Thanks in advance,
Chris