In Excel, how do I take a column of names and switch from "First Last" to "Last, First"?

Question

I have a column of 500 names that are in this format:

Mike Smith
Mark Jones
Matt Jenkins

And I need them to look like this:

Smith, Mike
Jones, Mark
Jenkins, Matt

Can someone come up with a formula to save me a TON of time today???

Thanks for reading, and in advanced for the help

Answer 1

Use the following formula in the first row of the col in which you want result, and then copy and paste it all the way down to the 500 rows.





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



Let's say you have names in Col A, from A1 to A500 and you want results in Col B, from B1 to B500 - paste the formula in Cell B1 - when you see the desired result in cell B1, copy the formula from B1 and paste it all the way to B500.



PS: Later if you want to get rid of the formula and just keep the values, you can copy cells B1:B500, right click on cell C1, select 'Paste Special'. In the paste special window, select 'Values Only', click OK to paste only the values and remove the formula -- this way, your cell C1:C500 will contain names in -- last, first-- format and will be independent of values in cell A1:A500.



HTH

Answer 2

This might not be the fastest way, but it's what I do.



First, I would want to break the names into two columns. I copy the names, paste them into a notepad document and save them. Then I import the txt file with the delimiter being "space" (i.e. the space between the names). That puts the first name in one column and the last name in a 2nd column.



Then I create a new column that contains a formula like this, for example:



=a2 & ", " & a1



where a1 is the first name and a2 is the last name.

Answer 3

Sorry to disagree with the Master there but try this out - under Data do a text to collums use delmited then next and click on space and click next and then finish this will put them in 2 collums - I would reccomend doing this on a seperate sheet or inserting a blank collumn after the names



Hope that helps

Answer 4

Here is your formula model that will convert a First Last in cell A1 to Last, First:

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



This formula takes everything after the first space character, adds a comma and a space, and then everything up to the first space character. Use this model to form your own formula, and copy/paste it into the column where you want your results listed, substituting your First Last cell for A1.

Answer 5

I would suggest first saving the file in RTF format, then import it as a CSV file, set the tabs, then it will become two columns in the new file. Then just swap the columns.



Surprised that a Master office type dude didn't know that.

Answer 6

If you had them in seperate columns, you can sort them, or exchange columns. If they are in the same columns there is not an easy way to do it, if there is.