Another one for the Excel gurus

Now, I know this can be done.....

I have a table, one column of which contains first & last names. I need to make it columns, one for each. I have over 400 names on this list and to upload it into my CRM package I need separate first & last names..

So, come on you clever people, how'd ya do it ??

There's probably better ways to do this (and I haven't used Excel for about 6 months so forgetting things rapidly) - try highlighting one column, cutting and pasting into a new sheet, then exporting that sheet into a CSV file - the CRM package will probably take CSV ok?

Then doing the same with the other column, natch.

EDIT: Scratch that - didn't read properly! Numpty!

Ooooh, another idea - find and replace, replace all spaces with a comma? Then export as a CSV and you've now got first and second names (assuming no middle names, no names which incorporate a space etc) as seperate columns?

In the cell you want first names in put

=LEFT(A1,(SEARCH(" ",A1)-1))

(A1 is the first cell where you have your list of names)

Where you want surnames put

=RIGHT(A1,(SEARCH(" ",A1)+1))

Then copy it down.

It looks for a space, then gives you all the characters to the left of the space for the first name and to the right for the last name.

Be careful, it MAY catch you out if you have some funny names with extra spaces.

HTH

You probably need the MID, LEN & FIND functions. You should be able to chop it up using the space in between first and last names?

Magic.. Should have thought of doing that myself. (kicks self in backside).

Cheers, sorted, Boss at head office will be very pleased now...

Originally Posted by MiniMatt

Ooooh, another idea - find and replace, replace all spaces with a comma? Then export as a CSV and you've now got first and second names (assuming no middle names, no names which incorporate a space etc) as seperate columns?

Pah, my solution is more elegant