Changing: Smith, John to John Smith

Veryconfused123 · Apr 16, 2011

Hi,

I have to copy names (one by one) from a Microsoft Outlook Address Book to an Excel Spredsheet. The names which I want to copy and paste are in the format, Last name, First Name e.g. Smith, John.

Once I copy & paste the names into Excel, what formula can I use to change them to First Name then Last Name e.g. John Smith I need the comma to be deleted also.

Also, is there a formula which will change ALL the names for me, not just the first one without an error?

If anyone could please help it would be great! I'm going to have hundreds of names = S

Thanks!

VoG · Apr 16, 2011

Try

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

Peter_SSs · Apr 16, 2011

Welcome to MrExcel!

Another alternative is:

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

Veryconfused123 · Apr 16, 2011

I found this formula:

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

It works great....only for cell A2. When I try and apply the formula to the cells below...this is what happens:

<table border="0" cellpadding="0" cellspacing="0" width="128"><col style="width: 96pt;" width="128"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 96pt;" height="20" width="128">Smith, John</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">John Smith</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Smith Joh</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Joh Smit</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Smit Jo</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Jo Smi</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Smi J</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">J Sm</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Sm </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20"> S</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">S</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="center" height="20">#VALUE!</td> </tr> </tbody></table>

VoG · Apr 16, 2011

Try like this

Excel Workbook

A

B

1

Smith, John

John Smith

2

Scuttle, Frederick

Frederick Scuttle

Sheet2

Veryconfused123 · Apr 16, 2011

Thank you both for your help!

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

Is not working for some reason

And the other formula works great but only for cell A2. How do I get it to work on any/all the cells?

Thank you!

Peter_SSs · Apr 16, 2011

Veryconfused123 said:
I found this formula:

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

It works great....only for cell A2. When I try and apply the formula to the cells below...this is what happens:

<table border="0" cellpadding="0" cellspacing="0" width="128"><col style="width: 96pt;" width="128"> <tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 96pt;" height="20" width="128">Smith, John</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">John Smith</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Smith Joh</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Joh Smit</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Smit Jo</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Jo Smi</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Smi J</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">J Sm</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Sm </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20"> S</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">S</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="center" height="20">#VALUE!</td> </tr> </table>

It would appear that you have a name in A1 and have placed the formula in A2 and copied it down. The formula is designed to be put in B1 (or C1 etc) and copied down with your original list down column A, as shown in VoG's last post. Here is mine again with afew names. The formula in B1 is copied down column B.

If your original list is not down a column and you can't modify this to suit, tell us what cells your original list is in and where you would want the new results to be.

Excel Workbook

A

B

1

Smith, John

John Smith

2

Jones, Ken

Ken Jones

3

Smith, Ann

Ann Smith

4

Johnson, John

John Johnson

Names

Veryconfused123 · Apr 16, 2011

It worked!!! Thank you so much!! I didnt know that you had to put the formula in B1.
This has honestly saved me so much time and stress! Thank you so much!!

Changing: Smith, John to John Smith

Veryconfused123

New Member

Excel Facts

VoG

Legend

Peter_SSs

MrExcel MVP, Moderator

Veryconfused123

New Member

VoG

Legend

Veryconfused123

New Member

Peter_SSs

MrExcel MVP, Moderator

Veryconfused123

New Member

Similar threads

Forum statistics

Share this page

Changing: Smith, John to John Smith

New Member

Excel Facts

Legend

MrExcel MVP, Moderator

New Member

Legend

New Member

MrExcel MVP, Moderator

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock