Reverse First Name - Last Name in a column

shariefi

New Member
Joined
Sep 20, 2006
Messages
6
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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
If A1 contains John Smith, this:

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

will return Smith John.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,497
Office Version
  1. 365
Platform
  1. Windows
.. another alternative:
=REPLACE(A1,1,FIND(" ",A1),"")&" "&LEFT(A1,FIND(" ",A1)-1)
 

shariefi

New Member
Joined
Sep 20, 2006
Messages
6

ADVERTISEMENT

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)
 

PGuru

Board Regular
Joined
Sep 16, 2006
Messages
55

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,497
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
1,136,995
Messages
5,679,037
Members
419,801
Latest member
spinjector

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top