2 Replies Latest reply on Aug 8, 2013 8:14 AM by philmodjunk

    Calculation Field - delete duplicate surname

    FionaWilson

      Title

      Calculation Field - delete duplicate surname

      Post


           Hi,

           I'm trying to get a return on a name field that removes one of the surnames if they are the same. For example, if the name is:

           Mr John Wilson & Mrs Sandra Wilson

           I want it to return:

           Mr John & Mrs Sandra Wilson

           This is the best I can come up with:

           If ( MiddleWords(Name; 3 ; 1 ) = RightWords (Name ; 1) ;

           Substitute( Name; MiddleWords(Name; 3 ; 1 ) ;  "") ;  Name  )

           But this returns:

           Mr John & Mrs Sandra

           So close - help please!

           Fiona

        • 1. Re: Calculation Field - delete duplicate surname
          shilpas@metasyssoftware.com

               Let(
               [
               strName="Mr John Wilson & Mrs Sandra Wilson"; //Here goes your name field
               x = If ( MiddleWords(strName; 3 ; 1 ) = RightWords (strName ; 1) ;
               Replace ( strName;  Position ( strName;  RightWords (strName ; 1); 1; 1); Length ( RightWords (strName ; 1)) + 1; "") ;  strName  )
               ];
               x
               )

                

               Note: This is applicable in scenario where third and last word of name field are same.

          • 2. Re: Calculation Field - delete duplicate surname
            philmodjunk

                 LeftWords ( strName ; 2 ) & Rightwords ( strName ; 4 ) would be simpler than the Replace and could be dropped into the expression supplied by MetaSys to get the same results.

                 It has the same limitations and I suspect that it thus won't work in all situations as some people have two word first and/or last names and this will cause the calculation to produce an unacceptable result.

                 For better results, I recommend putting the names into either 4 fields or 2 fields of two related records. This separates first and last names for better comparison and manipulation without relying on word counts.