Reverse First Name - Last Name in a column

[shariefi]

I've a two tables with 8000 records. Both tables contain name columns. One is organized 'Last Name ---- First Name'. The second is 'First Name -- Last Name. I am looking for a way to reverse the column in the second table to 'Last Name --- First Name' so that both tables are identical.
Help is greatly appreciated

 

[onlyadrafter]

Hello,

Are there only ever two names

e.g.

John Smith and no

John P. Smith?

if so is there a space that separates them?

if so try

=MID(A1,FIND(" ",A1)+1,LEN(A1))&" "&LEFT(A1,FIND(" ",A1)-1)

 

[Andrew Poulsom]

If A1 contains John Smith, this:

=RIGHT(A1,LEN(A1)-FIND(" ",A1))&" "&LEFT(A1,FIND(" ",A1)-1)

will return Smith John.

 

[Peter_SSs]

.. another alternative:
=REPLACE(A1,1,FIND(" ",A1),"")&" "&LEFT(A1,FIND(" ",A1)-1)

 

[shariefi]

you are right.
Yes, some of them do have a middle initial. One more questoin how do I apply the below solution across the entire column.
once again thanks to everybody

Hello,

Are there only ever two names

e.g.

John Smith and no

John P. Smith?

if so is there a space that separates them?

if so try

=MID(A1,FIND(" ",A1)+1,LEN(A1))&" "&LEFT(A1,FIND(" ",A1)-1)

 

[onlyadrafter]

Hello,

to use the formula for a whole column, select the cell with the formula provided, and drag/copy it down, cell refs to A1 will change as required i.e. A2, A3.

For middle initials look at this post

http://www.mrexcel.com/board2/viewtopic.php?t=28205

 

[PGuru]

This is a bit longwinded but it does work for middle names & middle initials.

=IF(ISERROR(SEARCH(" ",REPLACE(A1,SEARCH(" ",A1),1,""))),RIGHT(A1,LEN(A1)-FIND(" ",A1))&" "&LEFT(A1,FIND(" ",A1)-1),MID(REPLACE(A1,SEARCH(" ",A1),1,""),SEARCH(" ",REPLACE(A1,SEARCH(" ",A1),1,""))+1,LEN(REPLACE(A1,SEARCH(" ",A1),1,""))-SEARCH(" ",REPLACE(A1,SEARCH(" ",A1),1,"")))&" "&MID(A1,1,SEARCH(" ",REPLACE(A1,SEARCH(" ",A1),1,""))))

Essentially, if there is only one space in the field it goes through Mr Poulsom's formula, otherwise it strips out the first space in order to get at the surname, then sticks everything else in afterwards.

It will only work with one middle name though.

Hopefully someone with a bit more logic than me can simplify this!

 

[Peter_SSs]

shariefi

You have said that some contain a middle initial. I presume it is also possible that some would have a two word last name. What results would you expect for these?
Charles D Gaulle
Charles de Gaulle

 

[shariefi]

Thanx to all of you. The solution works .....