Need to split a column when Importing Excel into Access

[Crystal]

I receive Excel spreadsheets weekly from a customer. One
of the columns contains contact info with Name, Address
and Phone number and other info in one column. In the
past this was imported into one field in a table in
Access. I want to be able to split this us to multiple
fields to allow for more funtionality. I could easily
split this up if it was all separated by comma's or some
other delimiter BUT the data is not all consistent. Some
of the time the info is just separated by spaces or tabs.

Any ideas on how I could split the data? I need something
that will work everytime so that eventually I can automate
this process if at all possible.

Thanks!

[GreySky]

There's very little we can do without seeing an example of
every possible variation you may encounter.

I have a feeling this might be best solved by engaging a
contractor.

David Atkins, MCP

[Karrie]

Ask the customer to give you the data more consistently!
They probably don't even know that it matters.

>.
>

[Crystal]

Thanks for the help. I can't have the customer change
their process. I searched online and found some sample
code that I can modify for every possible variation.

>.
>

[John Nurick]

Hi Crystal,

There's no easy way of doing this; it's necessary to write code that
looks for patterns in the data (e.g. if you find two upper case letters
followed by a space and five digits, there's a good chance that the one,
two or possibly three preceding words are the name of a city). It's
absolutely impossible to write code that will do the job 100% because of
the huge number of variations that need to be taken into account.

If you want to try for yourself, start by experimenting with the
VBSCript regular expression object, which can do the sort of pattern
matching that's involved. There've been some threads on that in this
group lately: search (at groups.google.com) for my name and Robert
Neville's .

Alternatively, there is some commercial software designed for parsing
names and addresses. The cost unfortunately reflects the difficulty of
the task<g>.

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

[Jim/Chris]

Crystal,

One way to go about it is to split the names in Excel
before you import to access. I think I got this from one
of th MVP's or support sites. What would we do without
them?
Create two functions inside the function bar (equals bar
at the top):

A B C
1 Full Name FirstName LastName
2 Jim Jawn Jim Jawn

B2's Formula would look like this: =LEFT(A2, FIND(" ",
A2)-1)
C2's Formula would look like this: =RIGHT(A2, FIND(" ",
A2)+1)

You add and subtract 1 to account for the space. Once you
get that, you can=just drag the formula down the column,
copy the cells, the paste special values... Once you do
that, you can just reimport.

Jim

>.
>

[Jim/Chris]

I did not read your message correctly. What I sent was a
name splitter. Her is an URL that may help you. Article
number 37
http://www.helenfeddema.com/CodeSamples.htm
Sorry about that

Jim

>.
>

[John Nurick]

Hi Jim,

This approach is fine if there's just a first name and a last name in
the field, but in her original post Crystal said the file had

>Name, Address and Phone number and other info in one column

and that the data

>the data is not all consistent.

It's the inconsistency that makes it so difficult.

On Wed, 6 Aug 2003 07:15:33 -0700, "Jim/Chris" <crev...@excite.com>
wrote: