5 Replies Latest reply on Sep 2, 2012 4:37 PM by davidanders

    Extracting Names



      Extracting Names




      I have been trying to extract a single field into the appropriate First, Middle, Last, Title, etc. fields.

      using the help document

      Extracting Title, First, Middle, and Last Name from a Single Field 

      All works well, except for the Middle Name. I cannot get a name like Mary Elizabeth Smith to put the Elizabeth in my middle name field.  I have cut and pasted the information directly from the document above, using the exact same field names.


      Case(IsEmpty(Title) = 1 and MiddleWords(FullName; 2; 1) - LastName; MiddleWords(FullName; 2; 1); 
      IsEmpty(Title) = 0 and MiddleWords(FullName; 3; 1) - LastName; MiddleWords(FullName; 3; 1); "")

      Any ideas?





        • 1. Re: Extracting Names

          Your current field can contain how many words?
          Always 3? sometimes 2? seldom 4?
          What are your field names.

          I do not think that the  IsEmpty test is needed.

          If two words, are they ALWAYS first and last name? Probably.
          If three words, are they ALWAYS first, middle, and last name? Maybe.

          If there are a variable number of words, you would need to use WordCount function with LeftWord, MiddleWords, and Lastword and Case to parse the name correctly.

          FORMAT    WordCount(text)

          --from the extract names help page--
          MiddleName (Calculation, text result)=
          Case(IsEmpty(Title) = 1 and MiddleWords(FullName, 2, 1) lastname, MiddleWords(FullName, 2, 1),
          IsEmpty(Title) = 0 and MiddleWords(FullName, 3, 1) lastname, MiddleWords(FullName, 3, 1), "")

          From your post above

          Case(IsEmpty(Title) = 1 and MiddleWords(FullName; 2; 1) - LastName; MiddleWords(FullName; 2; 1);
          IsEmpty(Title) = 0 and MiddleWords(FullName; 3; 1) - LastName; MiddleWords(FullName; 3; 1); "")


          FORMAT   MiddleWords(text;startingWord;numberOfWords)

          FORMAT   Case(test1;result1{;test2;result2;...;defaultResult})





          EXAMPLE   Case(Score >= 90;“Excellent”;Score > 50;“Satisfactory”;“Needs Improvement”)

          FORMAT   IsEmpty(field)
          EXAMPLE     IsEmpty(OrderNum) returns 1 if the OrderNum field is empty.  
                           If(IsEmpty(LastName);“Invalid record”;““)
          displays Invalid Record if the LastName field is blank, but displays nothing if there is an entry in LastName.
          returns 0.

          CASE with IsEmpty
          Case (IsEmpty (Field1 & Field2); Field3
          ; IsEmpty (Field1) ; Field2
          ; Field1)

          • 2. Re: Extracting Names

            Case(IsEmpty(title)=1 and IsEmpty(Suffix)=0 and WordCount(fullname) ≤ 3;"";
                    IsEmpty(title) = 1 and WordCount(fullname)  ≤ 2;"";
                    IsEmpty(title) =1 and WordCount(fullname) >2;MiddleWords(fullname;2;1);
                    IsEmpty(title) = 0 and WordCount(fullname)  ≤ 3;"";
                    IsEmpty(title)=0 and IsEmpty(Suffix)=0 and WordCount(fullname) ≤ 4;"";
                    IsEmpty(title)=0 and IsEmpty(Suffix)=1 and WordCount(fullname) ≤ 3;"";
                    IsEmpty(title) =0 and WordCount(fullname) >3;MiddleWords(fullname;3;1);
                    IsEmpty(title)=0 and IsEmpty(Suffix)=0 and WordCount(fullname) ≤ 4;"";

            • 3. Re: Extracting Names

              Thank you to both of you who answered me. You gave me cause to go in and clean up as much as possible before pursuing further on this. Now, I have cleaned up 12,000 names, but it is still not perfect.  There are variable number of words in the current name field, even two names at times (don't ask... too many people maintaining the database, without enough forethought.


              So, I think the best thing to do now is to pull the first and last name, which is what I have working, as well as the Title.

              I can go in and hand-fix the middle name when necessary.


              My current problem is that now that I have first and last names pulled off, I can't edit those fields nor use them for future entries, as they are calculations. How do I handle this? My goal is to get rid of the single field that contains the long multiple-word name.



              • 4. Re: Extracting Names

                If you have the component calculations working correctly (or at least 'acceptably') you could define a text field for each of thiose names, and use the Replace Filed Contents menu function to set that calculated value into each of the new text fields.  Then you have what was a calculation as a manipulatable text field.

                Same result would be achieved by setting its value by Replace Filed Contents (by calculation) to be = the equivalent calculated field.