Extracting Text into separate cells

In an old spreadsheet, the user had created a single column to record names; these names take the format:

Mrs Smith
Mrs V Smith or Mrs VE Smith or Mrs VES Smith
Mrs Smith & Mr Smith
Mr G Smith & Mrs V Smith

In order to be able to use the spreadsheet for mail merging, I want to extract each part of the name into different columns. I have created 6 new columns to hold the three parts for 2 different names.

Any help would be much appreciated.

Select Range
Data Ribbon => data Tools Panel => Text To Columns Wizard

Pick "Delimited" instead of "Fixed Width"
Add "Spaces" to delimiters
Optional: change format of new output columns to "text" instead of "general"

This will split each "word" into one column. (note that the last row will create seven words; "&" will be in its own column; you can choose to skip the fourth column in the third step of the Wizard to drop the it if you'd like).

With formulas it might be possible to split it column wise...
But I would rather use the wizard here, and then find the surname as the last term of COUNT across each of the three columns in the row, or determine whether or not there's a MRS/MR term, and if there's a given name; do all that in another set of rows.

Then when I was done processing I'd copy/paste the values into the output functions, delete the input, and be done.

Ben,

I sometimes think that if I had a brain I would be dangerous! Many thanks for your prompt reply pointing out what was staring me if the face.

Keith