whats wrong with this spliting cell code

i have a list of people names in one cell, firstname and then surname. Eg. John Smith.


we are moving to a new accounts system and so for easy upload i need to split the names into their own cell. now i have added columns on both the right and left of the original data.
so here is my problem

so in cell AA3 i have the name John smith

in cell Z3 i have enetered the code
=LEFT(AA3,LEN(AA3)-FIND(" ",AA3))


in cell AB3 i have entered the code
=RIGHT(AA3,LEN(AA3)-FIND(" ",AA3))


now cell AB3 returns just the surname i.e 'Smith'

BUT im having problems with cell Z3 as it returns not only the firstname but the first letter og the surname i.e 'John S'

how can i get it just to return only the first name????

just copied the formula down another 20 lines and on some names the first name has appeared fine

John Heene

split properlly as 'John'

but on Rosie Coortnet

has come out 'Rosie Co'


whats is going on?????

after messing about with it, i have come up with a solution.

for anyone else having the same issues i did the following

1) selected the column where data was
2) Navigate to Data> Text to columns
3)selected 'delimited' from the wizard
4)now ensure that 'Tab', 'Space' and 'Comma' are checked
5) click finish

now they have been split into two.


There will, of course, be exceptions to this where the data has been laid out differently but this should provide a good basis.

Cheers

Originally Posted by royUK

Why not use Text to Columns?

Also, please take care with your titles, you are not using code, but an Excel Function.

sorry about the mistake on the title Roy, i used text to columns in the end.

Thanks