just some additional questions:
1. are you 100% certain all entries in the Name field contain only 2 values, first and last name? are there any names with a middle initial, suffix such as Jr., etc?
2. Are all names entered as "First Last", or are some formatted as "Last, First"?
3. will all future entry of names be entered separately, with "First Name " in its own field and "Last Name" in its own?
You will likely need to create two new calculation fields e.g. First Name, Last Name. The calculation for First Name is made using LeftWords ( text ; numberOfWords ) where text is the field that has the full name in it and the number of words you want is 1. Use Rightwords(text, 1) to get the last name.
You will immediately see problems with this where you have Jim Bob Smith or Jim Bob-Smith, or J. Bob Smith or any other number of permutations. Not sure how complicated or uncomplicated your full name field is but you can certainly start with this and build from there.
I have seen a number of elegant solutions for resolving the permutations described but I have not had the need for it myself so can't copy and paste such a solution for you.
1) Yes, the values in the Name Field contain only 2 values (a FEW have Jr., III, and sometimes "JIM & JILL SMITH" instead of just "JIM SMITH".
2) Yes, all names are entered as "First, Last"
3) Yes, all future entries will be "First, Last"
I already have the two fields set up as FIRST NAME, LAST NAME, and we already have about 5,000 existing names in this format, as well as ADDRESS, CITY, STATE, ZIP, etc., etc.
So...we just need to MOVE those darned Last Names into the 'LAST NAME" field. How do we do this??? I know there must be a solution...
1 of 1 people found this helpful
Here is how I would do it:
1) Create a temporary calculation called cNumWords with: WordCount ( nameField )
2) Create new FirstName and LastName text fields if you don't have them already.
3) YOu are not going to want to scroll through 5,000 names to check them but if the word count is two, you are pretty safe to use script to handle it.
4) Back up first
5) Perform a Find for 2 in this new calculation field.
6) Put your cursor in the FirstName field and Replace Field Contents (by calculation) with: LeftWords ( nameField ; 1 )
7) Put your cursor in the LastName field and Replace Field Contents (by calculation) with: RightWords ( nameField ; 1 )
8) Search for >2 in cNumWords and manually split them into their fields. If there are a lot, you can again use calc to split them if they all are identical in how to split them. What are you going to do with the Jr, III etc?
9) Search for 1 in cNumWords and fix.
10) I would leave nameField there or export it to safe location (along with your table's unique primary key ... you DO have one right?)
11) Only then, after the original set is safe and you are sure the translation was successful, delete the nameField and cNumWords calculation.
Thanks! This was brilliant!