Reversing “Last Name First” Lists
Imagine you have a column of names, each in a single cell, last name first. It could be in any table, but for this example, let’s say it’s in Excel. You need to make this into a first name last list for a mail merge. It’s 546 names long and you don’t fancy retyping them. Here’s a workaround. You’ll think there are a lot of steps, but once you’ve done it a couple of times, you can do it in seconds. Really.
Copy the cells and paste them into a new Word document. It will appear with the cell lines and all – it’s still a table
Click on the table to highlight it
Click Table in the menu bar (2007: Click the “Insert” tab and click on the drop-down arrow under “Table.”)
Click Convert (2007: Choose Convert Table to Text)
Choose Table to Text (2007: You’ve already done it. Move on.)
The names are currently each in a separate cell…when you move from cell to cell in Excel, how to do you do it? Right. By tabbing. So where it says, “Separate text with:” click on the button next to Tab
Now you have a list of last name first names, still in a column conveniently already highlighted.
Click Table (2007: Click the “Insert” tab and click on the drop-down arrow under “Table.”)
Click Convert (2007: Choose Convert Text to Table)
Choose Text to Table (2007: You’ve already done it. Move on.)
This is where you get to change the way it’s set up.
Change number of columns to 2
Change Separate text with to comma
Click OK
Now you have separated first name and middle initial from last name, but they’re still in the wrong order.
Click in the right column.
Choose Table (2007: Highlight your table – or just click in it anywhere)
Choose Insert > Column to the Right (2007: Click on the Table Tools Layout tab. Click on “insert right.” It’s near the left end of the tool bar when you have the Layout tab highlighted)
Move your cursor to the top of the new column until you have a fat arrow pointing down.
Click to highlight the new column.
Paste. Your last names are now after the first names. Highlight the spare column and delete it. There are other ways you could have done this – create a new column and paste the data into it, or highlight one of the columns and drag it to where you want it. That is the simplest. I didn’t tell you to do it that way in the first place because it doesn’t always work. Word can get snitty.
Now to get it out of the table:
Just do it again – Table> Convert> Table to Text.
Here’s the trick…this time Separate text with Other and enter a space into the box. If your list has no spaces before the last name or after the first name, it will come out perfectly. If it does, you’ll have a double space between the first and last name. A simple final step will clean that up:
Select all. Or unselect all – just make sure you don’t have a section selected.
Choose Edit>Replace or just your usual method of choosing Find, then click on the Replace tab. (2007: In addition to the usual key commands, you’ll find “Editing” at the right end of the tool bar when you’re in the “Home” tab.)
Click in the Find What box and hit the space bar twice.
Click in the Replace with box and hit the space bar once.
Click Replace All, Click Yes and Click OK
You’re finished. It was NOT that long. You’d have missed your whole lunch typing those 546 names. Order dessert.
Note: You can vary this system to suit the occasion-you may have to use semi-colons in the “Separate Text With” box if you’ve used an email list, etc. Just adjust appropriately. Middle initials can be a problem. The only solution may end up being 24 find and replace actions – one for each single letter followed by a period replaced with nothing.
Don’t see the answer to your question? Click here.
eMailed File is Showing Markup
Reversing Name Order in Mail Merge
Changing Picture Formats with Word
How to Use Word to Fix the Table You Want to Paste into Excel
