splitting text data from one field to two fields

[Manuel]

Hello everyone,

I am in need of some help. I have a database that I created years ago in
access 2000. I was using a field with people whole names were in a single
field separated by a single space. e.g. ("Halle Berry"). I am now trying to
split this field up into two fields one being [Last Name] and the other
being [First Name]. I know it would be simpler to retype, but I have a lot
of names to type. This is well over five hundred names or better. The new
database that I am designing needs to be able find a person by there [Last
Name]. Please help.

Thanks,
Manuel

 

[Douglas J. Steele]

To split First Last into two separate fields, you can use a combination of
the InStr, Left and Mid functions.

The first name will be Left(FullName, InStr(FullName, " ") - 1), and the
last name will be Mid(FullName, InStr(FullName, " ") + 1).

However, that will have an error for people with only one name (Cher, Pele,
Madonna, etc.). Also, what about 3 part names like Stevie Ray Vaughn or
Ludwig von Beethoven? In the first example, you'd want to split them into
Stevie Ray and Vaughn, in the second example to Ludwig and von Beethoven.

 

[Manuel]

Thanks Doug,

That works great. I was able to import all of the names. Some did have a
few errors, but it was minimal.

Manuel

To split First Last into two separate fields, you can use a combination of
the InStr, Left and Mid functions.

The first name will be Left(FullName, InStr(FullName, " ") - 1), and the
last name will be Mid(FullName, InStr(FullName, " ") + 1).

However, that will have an error for people with only one name (Cher,
Pele, Madonna, etc.). Also, what about 3 part names like Stevie Ray Vaughn
or Ludwig von Beethoven? In the first example, you'd want to split them
into Stevie Ray and Vaughn, in the second example to Ludwig and von
Beethoven.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello everyone,

I am in need of some help. I have a database that I created years ago in
access 2000. I was using a field with people whole names were in a
single field separated by a single space. e.g. ("Halle Berry"). I am now
trying to split this field up into two fields one being [Last Name] and
the other being [First Name]. I know it would be simpler to retype, but
I have a lot of names to type. This is well over five hundred names or
better. The new database that I am designing needs to be able find a
person by there [Last Name]. Please help.

Thanks,
Manuel