To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, click here to visit CFO.com’s Spreadsheet Tips page.
Cindi M wins an autographed copy of Guerilla Data Analysis Using Microsoft Excel for her question: “If you have a list of employee names in one column showing last name, first name (Smith, John), how do you convert the list to show first name last name (John Smith)?”
There are two different approaches to this problem, either a formulaic solution or using Text to Columns followed by concatenation. In this week’s article, you will see the formula solution.
Solving the Problem One Step at a Time
Say that the names start in A2 and run down column A. Although you can solve the problem with a single formula as shown in Figure 5 (below), it will be less intimidating to break this problem down into smaller problems.
Where Is the Comma?
In Figure 1 (left), the formula in cell B2 uses the FIND command to locate the first comma within cell A2. =FIND(“,”,A2) returns the value 6 because the comma is the sixth character in SMITH, JOHN.
The positional values in column B will be used by the LEFT and MID functions to retrieve the first and last names.
Getting the Last Name
The last name in each cell in column A starts in the first character and runs up to just before the comma. If you know that the comma is in the sixth position, then you want to get the leftmost five characters. The LEFT function will return the leftmost N characters from a cell.
The formula in C2 is =LEFT(A2,B2-1). This formula returns all of the characters up to but not including the comma. (Figure 2, below)
Many Choices for the First Name
The MID function is my choice for retrieving the first name. The function lets you specify a starting position and a number of characters. For example, =MID(“ABCDEFG”,4,2) would start at the fourth character and return two characters which would be the letters “DE”.
To get JOHN from SMITH, JOHN, you would want to use something like =MID(A2,8,4). It is easy enough to calculate the 8 as the starting position by using B2+2.
Some people will calculate the number of characters by using the LEN function to find the total number of characters. However, if you want to grab all of the characters to the end of the cell, you can instead ask for a larger number of characters than would ever be expected.