In microsoft.public.excel.misc on Sun, 29 Jul 2007, MartinW
wrote :
Hi Baffled,

If you have to deal with multiple surnames it may be better to
do it like this.
With your names in column A.
B1: =MID(A1,1,FIND(" ",A1)-1)
C1: =MID(A1,FIND(" ",A1)+1,50)
Drag both formulae down to the end of your data.
Select all col B and col C data
Right click and copy, then right click again and paste values.
Then delete col A.
Repeat the process for the address part.

The formula in B1 will return anything to the left of the first space
The formula in C1 will return anything to the right of the first space

That's a very useful pair of formulas - I often have to split text, but
never would have thought of doing it like that.

Thanks.
--
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)