This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Change Smith, Jane to Jane Smith
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: 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)).
Figure 255 The formula in column B achieves the result..