count how many times a word is repeated within a cell and build new simplified name strings

[pwl2706]

Hi,

I have a list of several thousand items, which consist of several different names together like this:

Mr P Thompson & Mrs S Thompson & Mr A Thompson
Mr C Guy-Johnson & Mrs A Guye-Johnson & Miss J Guye-Johnson
Mrs Fuller & Ms D Fuller & Dr K U Fuller
Dr V Patel & Dr OO Patel
Mr B Burden & Mr MP Wood & Ms C Pollock
Mr PW Philips & Mrs PW Philips
Dr D Watson & S Holmes
Mr R Polanski & Mrs S Polanski
Mr S Spielberg & Miss G Spielberg & Mrs T Spielberg

Sometimes the surname is repeated within the cell, sometimes it is not.

I want to build a formula that will determine if the surname is repeated, and return a string where the Salutations/titles and inititals are concatenated with the Surname at the end, unless the surnames are different.

For example,

- Mr S Spielberg & Miss G Spielberg & Mrs T Spielberg
- Mr R Polanski & Mrs S Polanski

would become,

- Mr S & Miss G & Mrs T Spielberg
- Mr R & Mrs S Polanski

BUT:

- Mr B Burden & Mr MP Wood & Ms C Pollock
- Dr D Watson & S Holmes

would remain the same as the surnames are different

Is it possible to do that with formulas, (and not splitting the names using Text to Columns), and how would I do that please?

thanks
Philip

 

[Gerald Higgins]

Try this.

It's a bit clunky, there may be better ways of doing it, but it appears to work.

=SUBSTITUTE(A1," "&RIGHT(A1,LEN(A1)-FIND("ZZZ",SUBSTITUTE(A1," ","ZZZ",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))),"")&" "&RIGHT(A1,LEN(A1)-FIND("ZZZ",SUBSTITUTE(A1," ","ZZZ",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

Comment - this relies on "ZZZ" not being found naturally in your data.
If it's possible that "ZZZ" may be found in your data, replace this with some other unlikely text string.

 

[Peter_SSs]

It isn't among the sample data, but I wonder if entries like this might occur?

Mr R Polanski & Mrs S Polanski & Mr S Spielberg

 

[pwl2706]

It isn't among the sample data, but I wonder if entries like this might occur?

Mr R Polanski & Mrs S Polanski & Mr S Spielberg

yes, thay can...

 

[Gerald Higgins]

So what do you want to do about that ?

My solution works by identifying the last word (or, to be more precise, any text after the final space character), and deleting any earlier examples of that word.
This is a way of identifying what the surname to be deleted, actually is.

It won't convert
Mr R Polanski & Mrs S Polanski & Mr S Spielberg
to
Mr R & Mrs S Polanski & Mr S Spielberg.

If you want to do that, you'll need to produce some other way of determining what the target surname is, i.e. that "Polanski" is the name to be deleted.

Unless someone has a great idea on how to do this, if I were you I would consider dealing with examples like this manually by exception. In other words, use my formula, or another that does the same thing, to deal with 99% of your data, and then look through the results manually. Investigate any unusually long text strings and correct them manually.

This will probably be quicker than trying to derive a formula solution to deal with all the possible permutations like this.

 

[pwl2706]

thanks Gerald, I think you're probably right on this...

 

[Gerald Higgins]

Last edited by pwl2706; Today at 03:53 PM. Reason: spelliong