5 Replies Latest reply on Oct 22, 2016 11:07 AM by oldgustav

    How do I "split" first name/last name into two separate files?

    MichaelKissinger

      Title

      How do I "split" first name/last name into two separate files?

      Post

      Can someone please tell me how to separate or split the data in FIRST NAME into First Name and Last Name fields?

      For example, I just imported a HUGE database, and the names are all like this: JIM SMITH, and "JIM SMITH" is all in ONE field.  However, I want JIM as a First Name, and SMITH as a Last Name field.

      So, how do I split this?

        • 1. Re: How do I "split" first name/last name into two separate files?
          ryan

          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?

          Thanks

          • 2. Re: How do I "split" first name/last name into two separate files?
            MaxEh

            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.

            • 3. Re: How do I "split" first name/last name into two separate files?
              MichaelKissinger

              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...

              • 4. Re: How do I "split" first name/last name into two separate files?
                LaRetta_1

                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.

                1 of 1 people found this helpful