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

# Calculation Field - delete duplicate surname

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

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

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.