transferring names from 1 excel spreadsheet to another

Select all the names in one column
Paste to new spreadsheet

Use Text to columns with a delimiter of space.
This should suffice if they all have a title (Mr Mrs etc) otherwise the names will shift left.

Give it a try see if it works.

Working on it...

Ok try this (try it anyway, we're only working on a new spreadsheet)

This assumes:
1) Every name has a title at beginning
2) There are no more than 3 forenames

copy the names to a new spreadsheet in column A

in B1
=SUBSTITUTE(A1," ",",")
and copy down

in C1
=LEN(B1)-LEN(SUBSTITUTE(B1,",",""))
and copy down

in D1
=IF(C1=1,","&B1,SUBSTITUTE(B1,",",REPT(",",5-C1),C1))
and copy down

Now use Text to columns on Column D with a delimiter of COMMA.
You should now have a "gridlike" chart with titles at beginning, surnames at end and christian names/middle names in between
Where there is no second or third name there wil be blanks
The titles, christian name and surnames should now all be aligned.

Use that grid to copy /paste value back to where you want the names to go.

Let us know how you get on (I've worked hard on this!) :-)