Reordering Last Name and First Name

Summary: If you've got a list of names in a column, you may want to change the order of each name. For instance, the name have the last name first, and you want to reorder the names so that they are in proper order. Here's how to do it. (This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, Excel 2003, and Excel 2007.)

You may have a worksheet containing many cells with names that are in LastName, FirstName MI format. A common example of this would be an Excel worksheet designed to work as an expense account reporting form. You may want to convert these employee names into standard format, i.e. FirstName MI LastName. Performing this operation on more than a handful of cells can become quite cumbersome.

To make the conversion job easier, you can use a handy formula that rearranges the parts of the name for you. Assume that cell A1 contains the name Doe, Jane Q. and you want the conversion (Jane Q. Doe) to appear in cell B1. Place the following formula in B1:

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

The formula works by breaking the string based on the placement of the comma. The formula will also work with suffixes and multiple middle initials as long as there is one and only one comma present in the source cell.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2168) applies to Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

More Power! For some people, the prospect of creating macros can be scary. Those who conquer their fears, however, find they become much more confident and productive once they learn how to make Excel do exactly what they want. ExcelTips: The Macros is an invaluable source for learning Excel macros. You are introduced to the topic in bite-sized chunks, pulled from past issues of ExcelTips. Learn at your own pace, exactly the way you want.

Check out ExcelTips: The Macros today!

Helpful Links

Newest Tips

Subscribe

RSS Feeds

Reordering Last Name and First Name