Separate full names

[Crystal via AccessMonster.com]

Hello,

I know that someone can do this easily with functions, but I just cannot seem
to figure this out. I need to separate last name and first name. I can do
last name but have trouble with first name. It has mid initial and other
strings attached. Here are my query and result:

LNM: Left([name],InStr([name],",")-1)
FNM: Mid([name],InStr([name],",")+2,InStr([name]," ")-1)

Source table:

Anew, Habit, MD
Anthony, Gold N., DO
Chen, Maria, LAC
Rotemowitz, Tryan L., MD
Clearani-Mullak, Ntooine H., MD

Here is the result:

Anew Habit
Anthony Gold N.,
Chen Maria
Rotemowitz Tryan L., M
Clearani-Mullak Ntooine H., MD

I need FNM to be FNM only and no other string attached. They should be as
Gold, Tryan, Ntooine, etc.

Thanks in advance for any help.

 

[Douglas J. Steele]

Unfortunately, it's a non-trivial exercise.

How are you going to treat Mary Lou Retton, the gymnast. I believe her first
name is Mary Lou, not Mary.

Mike Labosh outlines a good approach in
http://www.mcse.ms/archive147-2004-11-1255905.html
There's also a KB article about this at
http://support.microsoft.com/?kbid=168799

If you've got a bit of money to spend on this, you might also take a look at
Name splitter: http://www.infoplan.com.au/splitter/

 

[John Marshall, MVP]

and from the other side, there is George Bush Sr. Is his last name "Sr"?

John... Visio MVP

Unfortunately, it's a non-trivial exercise.

How are you going to treat Mary Lou Retton, the gymnast. I believe her
first name is Mary Lou, not Mary.

Mike Labosh outlines a good approach in
http://www.mcse.ms/archive147-2004-11-1255905.html
There's also a KB article about this at
http://support.microsoft.com/?kbid=168799

If you've got a bit of money to spend on this, you might also take a look
at Name splitter: http://www.infoplan.com.au/splitter/


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello,

I know that someone can do this easily with functions, but I just cannot
seem
to figure this out. I need to separate last name and first name. I can do
last name but have trouble with first name. It has mid initial and other
strings attached. Here are my query and result:

LNM: Left([name],InStr([name],",")-1)
FNM: Mid([name],InStr([name],",")+2,InStr([name]," ")-1)

Source table:

Anew, Habit, MD
Anthony, Gold N., DO
Chen, Maria, LAC
Rotemowitz, Tryan L., MD
Clearani-Mullak, Ntooine H., MD

Here is the result:

Anew Habit
Anthony Gold N.,
Chen Maria
Rotemowitz Tryan L., M
Clearani-Mullak Ntooine H., MD

I need FNM to be FNM only and no other string attached. They should be as
Gold, Tryan, Ntooine, etc.

Thanks in advance for any help.

 

[Douglas J Steele]

There are also compound last names such as Ludwig von Beethoven.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

and from the other side, there is George Bush Sr. Is his last name "Sr"?

John... Visio MVP

Unfortunately, it's a non-trivial exercise.

How are you going to treat Mary Lou Retton, the gymnast. I believe her
first name is Mary Lou, not Mary.

Mike Labosh outlines a good approach in
http://www.mcse.ms/archive147-2004-11-1255905.html
There's also a KB article about this at
http://support.microsoft.com/?kbid=168799

If you've got a bit of money to spend on this, you might also take a look
at Name splitter: http://www.infoplan.com.au/splitter/


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello,

I know that someone can do this easily with functions, but I just cannot
seem
to figure this out. I need to separate last name and first name. I can do
last name but have trouble with first name. It has mid initial and other
strings attached. Here are my query and result:

LNM: Left([name],InStr([name],",")-1)
FNM: Mid([name],InStr([name],",")+2,InStr([name]," ")-1)

Source table:

Anew, Habit, MD
Anthony, Gold N., DO
Chen, Maria, LAC
Rotemowitz, Tryan L., MD
Clearani-Mullak, Ntooine H., MD

Here is the result:

Anew Habit
Anthony Gold N.,
Chen Maria
Rotemowitz Tryan L., M
Clearani-Mullak Ntooine H., MD

I need FNM to be FNM only and no other string attached. They should be as
Gold, Tryan, Ntooine, etc.

Thanks in advance for any help.