Reversing Last Name First in Excel
You have a list of 2,437 names, first name and then last. You need a single cell with last name first. If you’re reading this, you’ve probably already discovered that you can’t merge cells without losing data. Here’s where the “Covert Text to Table,” “Convert Table to Text” and “Find and Replace” functions in Word are your friends: reversing last name first lists.
First I’ll tell you how to do this within Excel. Then I’ll tell you the way I prefer to do it, which is to make use of the Word table function.
If the list is already in two columns:
Create a new column. The examples assume that your names are in columns A & B, and your new, blank column is C.
To combine the first and last name in A1 & B1, enter
=A1&” “&B1 in cell C1.
There is a space between the quotation marks.
To reverse the order of the name, enter =B1&”, “&A1 instead.
There is a comma and a space within the quotation marks.
Once you drag your formula down to then end of the column, you will have all the names combined.
This is all nice and nifty, but now you have to deal with columns A & B. If you just removed them, your formula will no longer have a reference and instead of names, you’ll see “#REF!” You can either hide columns A&B, or you can copy column C and “Paste Special” right back into the same column. Once the Paste Special box open, click on the radio button”values.” This will paste only formula results and not the formula.
I’m not all that comfortable with this process.I prefer to move back and forth between Word and Excel. For one thing, I have to remember a formula in Excel – no chance. For another, I always fear for my life when I remove columns from Excel. I just know that I’ll remove the wrong one. Finally, I find this trick more flexible, and that list is never as straightforward and the examples you find in Help. Reading how to do it takes much longer than actually doingit. Truuuuusst me.
The Word trick:
Just copy your list from Excel and paste it into Word.
It will turn up as a table. Order your columns (add a column to the right, cut the first names, paste into the new column).
Now highlight everything and under Table, choose Convert Table to Text. The window will pop up and offer you choices of how to separate text. Click the radio button next to “comma.”
Highlight everything again, choose Replace (Find and then choosing the Replace tab works, or choose Replace under the Edit menu). Put your cursor in the Find box enter a comma. Put your cursor in the Replace box and enter a comma and a space.
Now just highlight everything, copy it, click into the first cell of the last name column in Excel and paste.
If your list is in one column, but in the wrong order:
When you paste your column into Word, click on the box that appears in the lower right and choose “paste text only.”
Highlight everything. Under Table, choose convert text to table. The window will pop up and offer you choices of how many columns and how to separate text. Enter “2” in the columns box and click the radio button next to “other.” Click the space bar once in the box next to “other” (you’ve chosen to separate your text with a space).
Order your columns (cut the first name, insert column to the right, paste to the right of the last names column).
Now reverse the procedure: highlight everything and under Table, choose Convert Table to Text. The window will pop up and offer choices of how to separate text. Click the radio button next to “comma.”
Highlight everything again, choose Replace (Find and then choosing the Replace tab works, or choose Replace under the Edit menu). Put your cursor in the Find box enter a comma. Put your cursor in the Replace box and enter a comma and a space.
Now just highlight everything, copy it, click into the first cell of the last name column in Excel and paste.
If you have middle initials or titles: It all becomes more complex. You’re going to have to play with it. Depending on the list, sometimes the easiest way is to Find and Replace all “A.” with nothing. Then do the same for “B.” until you’ve gone through the entire alphabet. You can find “ Jr.” and eliminate the space until you’ve got your list made, then go back with Find and Replace and put the space back in. What’s easiest depends on the length and complexity of your list.
Counting Multiple Variables in Excel
Everything Pasted into One Cell!
How to View Excel on Multiple Monitors
Need a copy of Excel? Want some more Excel tips? Buying through these links helps support this site.
