Jump to content

Get the last name out of full name field


This topic is 7786 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

I wonder how many people's last name will be Jr., Phd, III... wink.gif" border="0. 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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 7786 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.