data split to new column

[robin121600 via AccessMonster.com]

I developed a program where in member list has a separate column for LastName,
MiddleName and FirstName but my problem is they give me soft copy of more
than 5000 members in excel format where in the FirstName, MiddleName and
LastName are combined in 1 column, when i import it with my program i could
not split it in 3 columns, can anyone help me with my problem? do you know
any code or query to split this 1 column to 3 columns? thank you in advance.


robin

example:

in excel format (1 column) in ms access should be (3 columns)

header: Member List LastName MiddleName FirstName

Melissa Maria Speed Speed Maria Melissa
Richard Michael Smith Smith Richard Michael

 

[Dale Fye]

Robin,

Nothing is fool-proof when you get data formatted like this. This technique
will not work with split last names (like Von Kruger).

You should use the Left( ) and Mid ( ) functions, along with the Instr( )
and Instrrev( ) functions for this.

First Name = Left(MemberName, instr(MemberName, " ") - 1)
Last Name = Mid(MemberName, instrrev(MemberName, " ") + 1)
Middle Name = Mid(MemberName,
instr(MemberName, " ") + 1,
(instrrev(MemberName, " ") - 1) -
(instr(MemberName, " ") + 1))


--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Linq Adams via AccessMonster.com]

As Dale has suggested, this kind of thing tends to be a mare's nest. Does the
provided name ***always*** have a middle name? Is the last name ***always***
one word or can it be ***Del Rio*** or ***Von Landingham***?

One way to check the number of spaces in the name is to use something like

SpaceCount = Len([YourNameField]) - Len(Replace([YourNameField], " ", ""))

and then base your parsing on how many spaces (SpaceCount) the name has.
You'll still need to do a manual inspection to insure 100% conversion. A name
like

John Von Landingham

where Von Landinham as the last name will be parsed as Von being the middle
name and Ladingham as the last name unless manually corrected.

 

[robin121600 via AccessMonster.com]

Dale and Linq

I got you point. I did it from my program and it works but as you said I need
it to make some manual correction in order to be 100% accurate.

Thank you very much

Robin