split name into new cell in excel

[neoice]

Hello,

I have approx 200 names in the following format:-

CELL 1
John robert smith

The names are in one cell.I need to remove the middle name and then copy the surname into a new cell. For example:-

Cell 1 Cell 2
John smith

I am about to manually do this and wondered if there was a way to automatically do this?

Thanks in advance

Neil

 

[xlbo]

If everyone has a middle name and there is always one space between names, these'll do it:
for the forename
=LEFT(A1,find(" ",A1)-1)

for the surname
=RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1)+1))


Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[Wray69]

Or you could just do text to coluumns, by going to Data, Text to Colums and use the space as a delimeter and then delete the column with the middle names afterwards.

Regards,

Wray

 

[neoice]

Thanks very much guys. Both solutions worked. However the "text to columns" solution was the easiest.

the code would have been better if i could somehow increment the A1 to A2, A3 etc value. I tried to use the VB editor without much success.

Again, thanks

Neil

 

[Zathras]

neoice, that wasn't VBA code that Geoff gave you. Those were formulas, meant to go into unused cells in the first row and then copied down the 200 rows you needed to manipulate. Excel adjusts the row references automatically when copying formulas. The idea was to copy/paste/special values when finished and then delete the original data column.

BTW Geoff, Congrats on Tipmaster of the Week!

 

[xlbo]

Thanks Zathras - and an extra thanks for enabling me to not have to type in the same response

Finally - after > a year - top place on the board - woo yay


Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[KenWright]

You da man!!! Way to go!!

Best Wishes
Ken................