Another split text question

[baigrieja]

Help, I'm pulling my hair out on this one.

I have inherited a list of name, but need to split out the Title, Initials and Surname into three separate fields. Here is a small sample

Mr A D Smith
A & D Smith
A & D Smith & Sons
A D & Mrs A Smith
Miss A D Smith
Dr A D Smith
Ms A D Smith
Dr A D Smith & Partners
A D Smith & B F Brown

In each case, surname should be Smith, Smith & Sons, Smith & Partners or Smith & B F Brown.

If the field contains a Title, but not at the start i.e. A D & Mrs A Smith, then the initial field should be blank and the title field should contain A D & Mrs A.

Finally, the initials should retain any spaces between characters.

I have a couple of Excel functions that perform well on the basic entries, but these do not handle surnames with spaces in them i.e. Smith & Sons.

Can anyone out there solve this?

[olasa]

Here's a solution (or at least a beginning to a solution)

Hope it can help

Ola Sandström

[baigrieja]

That will just about work. Is there any way the formula can be modified to collect the extra data after the Smith & B F Brown?

[olasa]

Sorry.
Try now open the file now.

Ola Sandström

[baigrieja]

Ola

Thanks very much for all your help. I am now in a position to load all this amended data into our legacy system. I managed to amend your formulae slightly as some of my data had 8 different cells after being parsed.