# Reverse First Name - Last Name in a column

#### shariefi

##### New Member
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

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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)

If A1 contains John Smith, this:

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

will return Smith John.

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

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)

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

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!

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

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

Replies
2
Views
158
Replies
2
Views
136
Replies
4
Views
236
Replies
3
Views
150
Replies
5
Views
120

1,219,792
Messages
6,150,292
Members
450,949
Latest member
faizanmalik10

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back