Get the last name out of full name field

[Ratkins]

Hi, you guys have helped before - I hope you can now

I have a field with a full name eg Mr Rodney James Jones. I want to be able to sort alphabetically and of course it sorts based on "m" for Mr. There are over 5000 records.

I need to take out the last name and place it in its own field, then sort is simple. I think I need to write a script - something like, go to the name field - take all letters from the right - when you come to a space (between the last and middle names) stop and cut - then paste into a new last name field, but am at loss how to word it correctly for Scriptmaker.

Can anyone enlighten me. Thanks

[Chris Wood]

Define a new field "Sort_by_last_name", make it a calculation usine RightWord(text, number of words), where "text" is your field containing the full name and "number of words" is 1. This will return "Jones" in your example.

Sorting on the "Sort_by_last_name" field will put the records in the correct order.

Good luck

Chris

[LiveOak]

I wonder how many people's last name will be Jr., Phd, III... . The lesson here is to always use separate fields for first name, last name, etc. The variety of ways names might be entered in a file of 5000 names will astound you. There is no 100% solutions for breaking out the last name (even if you have a person review all the records, there will still be some ambiguous entries). -bd

[WBlanchard]

I area with LiveOak, it is always easier to break information down into its small component, and then build it up as needed. I would create field for Salutation (Mr., Mrs., Ms., Dr. etc.), First Name, Last Name, Suffix (Jr., Sr. III, etc) and finally Title (PhD, ACE, CPA, etc).

From here you create any form field you want by using a calculation field and concatenation (&) such as:

Last_First = "Last Name" & ", " & First Name [etc]

The possibilities are almost infinite.

HTH

[Chris Wood]

Fortunately, back in the old country (UK) no one uses Jnr. Snr. or III after their surnames!!!

Chris

[Moon]

Chris, hopefully you don't have any Dutch, German or Spanish names in the old country either. Van Der Beek, Von Houptman, or El Dorado can be just as problematic as jr., sr., or III. And what will you do with the occasional "PhD" ?

Take LiveOak's advice and get your name fields parsed.