Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Breaking up address fields

0 views
Skip to first unread message

Chuck W

unread,
Feb 25, 2003, 12:00:05 PM2/25/03
to
Hi,

I received a quickbooks file that has name, address, city,
state and zip all in one field. There is nothing that
separates the values from each other except for a space.
Here is an example of what is contained in a single field.

John Smith 321 Main Street Tampa Bay, FL 30323-4564
Bill Johnson 4563 Oak Ave. Atlanta, GA 30319

I would like to find a way to break this field up into
name, address, city, state and zip. Any ideas?

Thanks,

Chuck

RobFMS

unread,
Feb 25, 2003, 12:40:34 PM2/25/03
to
Chuck

If you have the chance to contact the individual who gave you the file, I
might suggest asking him/her to see what can be done to give you a DELIMITED
file. At least this way you will know what constitiutes each field
information.

Otherwise, this gets rather complex. For such reasons a:
1) You do not know if you are getting FN MI LN information or FN LN
information (assuming FN is listed first then LN next)
2) How many "words" are in the address. Such that 101 Risky Way Drive versus
202 Park Drive
3) How many "words" in the city. Such that San Francisco versus Atlanta

If you are absolutely stuck with this file, then here might be some
suggestive tips:
For each record:
1) Find the FIRST numeric character starting from the left and moving right.
At this point, ASSUME everything to the left is part of the NAME field (FN
LN or FN MI LN). Mark the start of this location.

2) Starting from the RIGHT and moving LEFT, find the LAST numeric character.
At this point, ASSUME everything to the right is part of the ZIP CODE field.
Mark the start of this location.

3) Everything between the two marked points is part of the ADDRESS field.

I hope this helps in some way.
Good Luck!

--
Rob
www.fmsinc.com
www.fmsinc.com/consulting

"Chuck W" <ch...@aol.com> wrote in message
news:04f901c2dcef$5855aee0$8ef82ecf@TK2MSFTNGXA04...

Roger Carlson

unread,
Feb 25, 2003, 12:41:37 PM2/25/03
to
On my website is a small sample database called "ParseStuff.mdb" which might
help get you started. The sample parses names, but the same methods will
work in your situation as well.

--
Roger Carlson
www.rogersaccesslibrary.com

"Chuck W" <ch...@aol.com> wrote in message
news:04f901c2dcef$5855aee0$8ef82ecf@TK2MSFTNGXA04...

JethroUK

unread,
Feb 25, 2003, 3:23:30 PM2/25/03
to
if it's not confidential you can post it here:

http://groups.msn.com/JethroUK

and i'll break it down

post it as text (.txt) file preferably - if it must be in Access database,
make sure it's Access 97

"Chuck W" <ch...@aol.com> wrote in message
news:04f901c2dcef$5855aee0$8ef82ecf@TK2MSFTNGXA04...

0 new messages