Splitting text

Hi I have written 2 formulas to split a single cell contents and it works up to a point. The problem I have is that the text entered, in this case a name, takes many different formats.

Current formulas are as follows:-
=UPPER(IF(ISERROR(FIND(" ",'Calc Sheet'!Q2,1)),LEFT('Calc Sheet'!Q2,FIND("",'Calc Sheet'!Q2,1)-1),LEFT('Calc Sheet'!Q2,FIND(" ",'Calc Sheet'!Q2,1)))) (for First Name)
and
=UPPER(IF(ISERROR(FIND(" ",'Calc Sheet'!Q2,1)),'Calc Sheet'!Q2,RIGHT('Calc Sheet'!Q2,LEN('Calc Sheet'!Q2)-FIND(" ",'Calc Sheet'!Q2,1)-0))) (for middle & last name)

The data I have i each cell is as shown below.

Miss Kelly Jones
Dr V Smith
Jane Brown
Jennifer Linton Cook
Mrs Brown.

etc.

I am trying to write a formula to split these various formats into 3 cells

Title First Name Last name
Miss Kelly Jones
Dr V Smith
Jane Brown
Jennifer Linton Cook
Mrs Brown

Is there any way that this would be possible.

Any help or guidance will be greatly appreciated

Macke

Hi yes it is very messy. These are downloads from Amazon sales and it seems that they allow customers to load data any way they like. I have played around with vlookups against the the title, but it ain't very easy.

Anyway thanks for your suggestion.

Ken