MS Access - splitting a fullname with/without suffix

Hi Experts,

I have this date:
Dr. Jennifer Bunk
Paula Watson
Dr. Therese Macan
Loriann Roberson & Regina Kim
Dr. Rodger W. Griffeth
Dr. Lisa Finkelstein
Dr. David P. Costanza
Dr. Sylvia Roch
Dr. Rosalie Hall
Dr. Andrea Snell
Dr. Wally Borman
Lucy Schurer Lambert
Robert P. Tett, PhD
Lucy McClurg
Carol Shoptaugh, PhD
Dr. Miguel E. Martinez-Lugo
Dr. Janelle Gilbert
Doctoral Programs Office
Dr. Jane R. Williams
Dr. Roya Ayman
Dr. Roya Ayman
Professor Jone Pearce
Dr. Roni Reiter-Palmon
Dr. Roni Reiter-Palmon
Department of Psychology
Director, PhD Program
Nathan Bowling
Dr. Brian W. Schrader
Dr. Betsy Shoenfelt
Dr. Maria Kraimer
Katie Ear
Paul Thomas
Dr. Comila Shahani-Denning
Elaine Nguyen, PhD Program Office
Dr. Shahnaz Aziz
Uma Janardana Iyer
Dr. Diane Roe
Eric Heggestad
Daniel Turban
Dr. Sebastiano Fisicaro
Dr. Doug Cellar, I-O Program Director
Dr. Mark Nagy
Jay Caughron
Paul S. Goodman
Carolyn Jagacinski
Sam Hunter
Dr. Robert Sinclair
Dr. Robert Sinclair
Jinyan Fan
Dr. Donald P. Schwab
Joel T. Nadler, PhD, Program Director
Dr. Alan Witt
Dr. Daisy Chang
Dr. Bruce M. Meglino
Ms. Jan Westbrook
Thomas Mitchell, Ph.D.
Dr. Aaron Schat
Michael A. Surrette
Alison Fragale
Dr. Mike Hein
Derek Chapman
Dr. Vincent J. Fortunato
Lisa Ordonez
Celeste Pape
Prof. Michelle Duffy
Jason L. Hicks, Ph.D.
Dr. Donald Truxillo
Dr. Lucy Gilson
Kerri Pickel, Director of Graduate Studies
Karen Eichelbrenner
Robert P. Tett, PhD
Jeanette Cleveland
Dr. Timothy Huelsman
Eric Marcus, Ph.D.
Dr. Brent Scott
Dr. Oney D. Fitzpatrick
Michele Gelfand
Naomi Thomas
Terry Beehr
Dr. Howard Tokunaga
Dr. K. G. Smith
Charles Scherbaum
Dr. Arthur P. Brief
Dr. Bart Weathington
Stephen J. Vodanovich
Dr. Mindy Bergman
Dr. Donald Hantula
Dr. Seth Kaplan
Dr. Lou Buffardi
Becky Barker/Assistant Dean for Student Services
Becky Barker/Assistant Dean for Student Services
Dr. Roseanne J. Foti
Dr. Dev Dalal
Dr. Don Ferrin
Dr. Dan Newman
Dr. Patrick  Knight
Dr. Doug Brown
Dr. Doug Brown
Dr. Madeline Heilman
Marc Skurski
Dr. Nathan Kuncel
Morgan Sammons, PhD, ABPP
Dr. Lisa M. Perez
Morgan Sammons, PhD, ABPP
Morgan Sammons, PhD, ABPP
Morgan Sammons, PhD, ABPP
Dr. Chockalingam Viswesvaran
Dr. Bryan Porter
Jacqueline Deuling, PhD
Dr. Brian Hoffman, Chair
Stephane Cote
Nathan Bowling
Dr. Jennifer Bragger
Dave Ferio
Shane Connelly
Tim Ahlberg, Assistant Director
Dr. Tom Wooldridge
Dr. Lisa Steelman
Dr. Gary Johns
John Hausknecht
Dr. Kim Smith-Jentsch
Chris Wright
Dr. Lori Francis
Dr. Larry P. Wiley
Libby Smith, M.S.
Dr. Heather M. McGee
Office of Graduate Studies
Keith Hattrup
Bart Craig
Prof. Francis J. Yammarino
Paul Paulus, PhD
Dr. Sandra Carpenter
Dr. Vikas Anand
Dr. Karl Aquino
Dennis Duchon
Dr. Faith-Anne Dohm
Patricia Petty, Program Manager
Ann Marie Gooch
Dr. Robert C. Liden
Dr. William L. Gardner
Dr. Ron Landis
Prof. T. Cox
Dr. Karen Ortlepp
Diana Bilimoria, PhD, Chair
Terry Beehr
Dr. Lisa Steelman
Dr.Stephanie Castro
Dr. Heather M. McGee
Sarah Brazaitis, Gina Buontempo, Lynda Hallmark
Dr. Shane Connelly
Dr. Douglas Kruse
Dr. Richard Harvey
Dr. Chieh-Chen Bowen
Morgan Sammons, PhD, ABPP
Office of Admissions
Rebecca Loehrer
Toni DiDona, PhD
Dr. Miquel Martinez-Lugo
Ruth Rohr Arden, Program Coordinator
Jaime Henning
Admission Office
Dr. Henry L. Kaplowitz
Rachelle Louison
Admissions Department
Dr. Carole Burgoyne
Joanne Harris
Dr. L. J. Purvis
Professor Michael O'Driscoll
A/Prof Liz Jones
Prof. Roy Payne
Tom Daniels
Lucy McClurg
A/Prof Liz Jones
A/Prof Liz Jones
Dr. Gary Johns
Diane Keyser Wentworth, PhD
Morgan Sammons, PhD, ABPP
Morgan Sammons, PhD, ABPP
Naomi Gardberg, Ph.D.
Barbara Fritzsche, PhD
Dr. Gisela Wendling, Coordinator
Kimberly T. Schneider
M. Gloria Gonzalez-Morales
William Martin, Ph.D.
Kathleen Zimmerman-Oster, PhD
Chris Waples
Carol Buchanan Jones, PhD
Dr. Ana Maria Corredera
Dr. Olga Sharp, Program Director
Dr. Felice Tilin
Val Van Domelen
Danielle Edwards-Crandall
Richard Andrulis, PhD
Daren Protolipac
Dr. Gregory Pool
Paul Thomas
Morgan Sammons, PhD, ABPP
Dr. Cong Liu
Kathleen Fuegen, PhD
Dr. Cheryl Stenmark
Brigitte Steinheider
Terri Cramer
Office of Admission
Dr. David M. Kopp
Dr. David M. Kopp
Brianna Piedmonte
Brianna Piedmonte
Prof. H. G. Kaufman
Dr. Lori Francis
Dr. Robert Akuamoah-Boateng
Alia Allen
Kimi Tippett
Sven Kepes, PhD
Paul Paulus
Patricia A. Oswald, Program Dir, MA in I-O Pysch
Dr. Mark Lengnick-Hall
Stacia Werner
Sylvia Roch
Katie O'Hare
OB/HRM Graduate Field Coordinator
OB/HRM Graduate Field Coordinator
Kurt Kraiger
Ted Rosen, PhD
Marin Moder
Dr. Natalia Lorinkova
Jaslyn Choo
Tammy Fitzpatrick
Katie O'Hare
Frank Igou, PhD
Carolyn Schneider
Carolyn Schneider
Dr. Joanna Palmer
Dr. Robert Haussmann, Dean
Dr. Robert Haussmann, Dean
Adrian Thomas
Dr. Alan Clardy
Dr. Michelle Hammond
Dr. Mark Bowler
Teresa M. Lyons, PhD
Office of Admissions
Jonathan Schramm
Jim Hadley
Graduate Admissions Office
Dr. Alexandra Luong
Benjamin Elman, Program Director
Karl Kuhnert
Pete Davies
Nancy J. Stone, PhD
Toni DiDona
Richard A. Mendelson
Richard A. Mendelson
Nancy J. Stone, PhD
Christy Peters
Ms. Anna Loh
Dr. Timothy Huelsman
Samantha Paustian-Underdahl
Simon Moon, Ph.D.
Dr. Crystal Harold
Jessica Nicklin

Open in new window

I would like to split into

Title(if present), First Name, Middle Name, Last Name, Suffix(if present)

This is done in MS Access 2010

Thanks for helping
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Once a name is mushed as yours are, it is pretty much impossible to extract it back into its component pieces and that is why proper designs never mush names.  Given what you have, you will need to decide how much effort you want to put into getting every entry correctly parsed.  I'll give you some general suggestions on where to start.

Use the Replace() function to get rid of any punctuation.  You could have periods and commas.  Getting rid of them will simplify the lookup list.  If you don't remove them, you'll need both "Dr" and "Dr." as well as ", Sr", ", Sr.", and "Sr".

I would create a table of prefixes and suffixes.  You can also use this as a way of standardization.

Use the Split() function to separate all the parts.
Then analyze the parts.  Use the prefix and suffix tables on the first and last fields.  Then for the middle part, if you have only two unused items, you can assume they are first name and last name.  If you have more than two, you need to decide how to proceed.  If your table has only a few hundred rows, I would simply handle these manually.  If you have thousands, you will need to decide what to do.  Some of those remaining names will be multi-field first names (Peggy Sue), some will be multi-field last names (St. James), others will be middle names, which also might be multi-part.  

Looks like you  also have multiple names in the string.  What are you going to do with those?  Are you going to create additional records, are you going to create multiple first, last, etc columns (really bad idea)?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Amour22015Author Commented:
Ok, thanks, I created a function to take care of and like you mention it is not going to be perfect.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.