Separate full names

  • Thread starter Thread starter Crystal via AccessMonster.com
  • Start date Start date
C

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.
 
and from the other side, there is George Bush Sr. Is his last name "Sr"?

John... Visio MVP

Douglas J. Steele said:
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)


Crystal via AccessMonster.com said:
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.
Click to expand...
Click to expand...
 
There are also compound last names such as Ludwig von Beethoven.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


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

John... Visio MVP

Douglas J. Steele said:
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)


Crystal via AccessMonster.com said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top