re-arranging data

Microsoft Exchange server 2007 standard...
April 1, 2010 at 06:24:51
Specs: Windows Vista
Excel - I have a list of 300 names but should have listed them last name, first name. How can i chage this around without re-typing all this data?

See More: re-arranging data

April 1, 2010 at 07:11:36

Is this a list in an Excel workbook?

Are first and last names in one cell, e.g., A1 contains "Jane Doe"

If so in cell B1 enter this:

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

The formula can be extended by dragging for more conversions.

If your names are in a different pattern, such as separated by commas, post an example.

If there are middle names or middle initials, post some examples.

If the above formula works for your data, finally select the cells in column B with the conversions, right-click and copy, select the source cells (same number of rows) and right-click and select PasteSpecial... and select Values.


Report •

April 1, 2010 at 07:27:23
WoW. I don't know how you guys come up with this stuff but it sure works. I can go back and take middle initials out of the orignal entry to make it easy but what if the name is for example

J. Jane Doe

Report •

April 1, 2010 at 07:48:52
re: I don't know how you guys come up with this stuff ...

Some of it we come up with on our own, and some of it we "borrow".

For example, to help with your question, I might borrow some stuff from here:

Report •

Related Solutions

April 1, 2010 at 07:55:23
Thanks again for your help. Saved me a day of work.


Report •

April 1, 2010 at 11:28:57

Try this formula, it is basically three options, depending on whether the source cell contains a period and whether the period is the second character.

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

I have split the formula on to several lines for ease of viewing.

Jane Doe	Doe, Jane 
Jane J. Doe	Doe, Jane J.
J. Jane Doe	Doe, J. Jane 


Report •

April 1, 2010 at 11:46:49
Another option is to use Data...Text to Columns to split the names into separate cells and then Concatenate them back together in any order you want.

=B1 & ", " & A1

Of course, this will only works for some names.

If you've got a mixture of 2, 3, or even 4 word names in various configurations, it might be hard to find a single formula or method to get them all in the order you'd like.

I have a client whose legal name is in the following format:

J. Stanley D. Doe

If you ask him for his first name, he'll tell you Stan.

Even Humar's fine formula above is going to have trouble with that one!

I deal with list of names fairly often so I know how much of pain rearranging them can be. I usually settle for whatever method gets the majority of them looking like I want and then I manually fix the rest.

Report •

April 1, 2010 at 11:59:10

It certainly does (have trouble with that one).

As DerbyDad03 said, get the bulk of the change done with a formula or process and pick up the odd ones manually.


Report •

Ask Question