Excel formula to split complex names

[pwl2706]

Hi all,

I have been tasked with some string manipulation and today must be my bad head day as it is proving more difficult than I expected.

I have to take the initials of the first and second and third name from the first and second and third columns along with any surnames

The reason is we have to fit the long names onto cheque books for customers we are taking on from another company.

Plus we need to keep the title.

Here is an example of the long name as it stands now:

Mr C Chrysostomou & Mr N Chrysostomou & Mrs A Chrysostomou
Mrs M Karseras & Ms P Hadjisoteriou & Mrs E Athanasiou
Mrs A Theodorou & Mr A Aristotelou & Mrs G Naziri & M Karmiou
Mrs L Vazanias & Mrs G Braithwaite & Mrs Helen West
Mrs L Vazanias & Mrs G Braithwaite & Mrs Helen West
Mrs Olympia Pieridou & Mrs T&mr M & Mr C & Mrs K Michaelides
Miss J A Santamas& Mrs M T Santama- Solomonides& Mrs Lida Santama
Miss J A Santamas& Mrs M T Santama- Solomonides& Mrs Lida Santama
Mr Polydoros Polydorou & Mrs Maro Themistocleous & Mrs Sylvia Polydorou
Mr Themis & Mrs Androulla & Mr Nicholas & Mrs Vasso Gina Demetriou
Mrs S K Makkofaides & Mr Z Koullas & Mrs Y Koullas & Mrs R Kleopa
Mr G Zorzy & Mrs H Louca Zorzy & Mr S Stavropoulos & Mrs Y Stavropoulos
Mrs M Franceschina & Ms C Eugeniou & Ms O L Toumazides T/a The Three Cupcakes
Mr David & Mrs Eileen Nixon D.h.nixon & Co. - Office Account

as you can see, these could be considered to be joint bank accounts between 2 or even 3 persons. on the cheque books we will have to put the title, which could be Mr, Miss, Ms, Dr, Doctor, or even (heaven help us) Messrs along with the initials of the first and second names and the full surname, and the total should be less than 35 characters !
so, here is what I have been trying after some searching on the web:

=IF(LEN(TRIM(E:E))-LEN(SUBSTITUTE(TRIM(E:E)," ",""))>=1,MID(TRIM(E:E),FIND(" ",TRIM(E:E))+1,1),"")& " " &IF(LEN(TRIM(E:E))-LEN(SUBSTITUTE(TRIM(E:E)," ",""))>=2,MID(SUBSTITUTE(TRIM(E:E)," ","",1),FIND(" ",SUBSTITUTE(TRIM(E:E)," ","",1))+1,1),"")

that gets the initials, but only the first 2

=RIGHT(J:J,LEN(J:J)-FIND(" ",J:J)+1)

gets the surname but isn't working correctly.

am I over thinking this, or under thinking it?

What is my best approach to the data?

thanks
Philip

 

[Peter_SSs]

1. Given those 14 rows of sample data, what would be the 14 expected results?
You'll also need to ensure we can understand how to derive the expected results, particularly for any unusual data (eg the second last row looks quite different)

2. Are there ever just single names (eg Mrs Ann Jones) among the data? If so, ensure you gives us a representative set of data & results.

 

[pwl2706]

well, for example, for this:

Mr Polydoros Polydorou & Mrs Maro Themistocleous & Mrs Sylvia Polydorou

I would end up with

Mr P Polydorou & Mrs M Themistocleous & Mrs S Polydorou

it seems like I need to write a custom Tokenizer or something!

 

[Peter_SSs]

well, for example, for this ..

Unfortunately that didn't really address either of my questions.

Nor have we seen how a "(heaven help us) Messrs" might occur & be dealt with. Examples?

You said that "the total should be less than 35 characters". Your example result in post #3 is longer than that. Perhaps you meant each individual name must be less than 35 characters but it isn't clear. Nor has any explanation been given as to what to do if whatever it is turns out to be 35 or more characters long. Does it just get cut off at 34 characters? Would you drop titles? Would you omit full names to achieve this? Something else? Examples?

I'm not sure I would be able to help with this anyway, but I doubt you'll get much useful help without clarifying such issues.

 

[pwl2706]

I think we're going to drop off the titles, whatever they are if they exist, bearing in mind that some of the names aren't given titles. Also, as you have poited out, even without the titles, and with just the initials and the surnames, it will be too big

thanks anyway, I'll ask for clkaarification from the business analyst and return!