Change Smith, Jane to Jane Smith

December 27, 2021 - by Bill Jelen

Problem: I have a column of names in last name, first name style. How can I convert the data to first name last name?

Strategy For Excel 2013: Flash Fill comes to the rescue! Type a heading in B1. Type Jane Smith in B2. Type E in B3. Flash Fill will preview the rest of the column. Press Enter. You are done.

Strategy for Excel 2010: While you could do this in many steps, using Text to Columns and then a concatenation formula, a single large formula would also solve the problem. To begin, you need to insert a blank column after column A to hold the calculation.

=FIND(“,”,A2) will locate the comma within the value in column A. In Smith, Jane, the comma is the sixth character, so the FIND function would return a 6.

The first name starts two characters after the result of the FIND function. It extends to the end of the text. You can use the MID function to isolate the first name. The MID function requires some text, a starting location, and a length. If you ask for more characters than are in the text, then Excel will return from the starting position to the end of the text. For example, if you ask for 50 characters, Excel will handle any first name that has 50 characters or less. Therefore, you use =MID(A2,FIND(“,”,A2)+2,50).

The last name is always the leftmost characters, so you can use =LEFT(A2,FIND(“,”,A2)-1).

To join the first name and last name together, you concatenate the function for the first name, a space in quotes, and the function for the last name. You need to be sure to leave the = sign off the LEFT function because you don’t prefix the function with an equals sign when it occurs in the middle of the formula.

If you want the text in uppercase and lowercase, you need to wrap the entire function in the PROPER function. As shown below, the formula is =PROPER(MID(A2,FIND(“,”,A2)+2,50)&” “&LEFT(A2,FIND(“,”,A2)-1)).

This article is an excerpt from Power Excel With MrExcel

Title photo by Kinson Leung on Unsplash