Editor’s Note:

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.

Fig. 1

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)

Fig 2

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.

My formula in D2 is =MID(A2,B2+2,50).  (Figure 3, below). This will return the 4 letters of JOHN. I often have people ask if Excel will add an additional 46 spaces after the name to make the whole 50 characters. Since Excel does not pad the answer to force it to be 50 characters wide, this approach is simpler than the alternative formulas:

=MID(A2,B2+2,50)

=MID(A2,B2+2,LEN(A2)-(B2+1))

=RIGHT(A2,LEN(A2)-(B2+1))

Fig. 3

Joining the Text Back Together

The fancy word for joining cells is “Concatenation”. Some people might use the =CONCATENATE() function to join the text. I find it easier to use the & sign which acts like the plus sign, but for adding text. So the formula, written =D2&C2 would join the values together like JOHNSMITH. Since you will want a space between the names, use =D2&” “&C2 to join together the first name, a space, and the last name.

If you want to convert the name from capital letters to proper case, use =PROPER(D2&” “&C2), as shown in Figure 4, below.

Fig. 4

Using a Single Formula to Solve the Problem

If you are new to FIND, LEFT, MID, and PROPER, then it helps to build the formula one step at a time. If you get to the point where you understand all of those functions, you can build a single formula in B2 to solve the problem. The formula, which would be written out in one long string — as illustrated in Figure 5 — would look like this:

=PROPER(MID(A2,FIND(“,”,A2)+2,50)&” “&LEFT(A2,FIND(“,”,A2)-1))

Fig. 5

Don’t Forget to Convert the Formulas to Values

Before you delete the names in column A, you need to convert column B from formulas to values. This is almost as simple as a Copy and Paste, with one twist. The steps are different in various versions of Excel, and I’ll point out the differences below.

Converting Formulas to Values Excel 2010

Select column B. Use the Copy command on the Home tab. Open the Paste dropdown and choose the large letter A to paste values (Figure 6, below).

Converting Formulas to Values Excel 2007

Select column B. Use the Copy command on the Home tab. Open the Paste dropdown and choose Paste Values.

Converting Formulas to Values Excel 2003 and Earlier

Select column B. Use the Copy command on the Edit Menu. From the Edit menu, choose Paste Special. In the Paste Special dialog, choose Values and click OK. (Figure 7,  below).

Bill Jelen is the author of 32 books about Excel including Excel Gurus Gone Wild. You can win a copy of one of his books if your question is selected for a column. Post your question to the community content block at the right.

 Fig 6

Fig. 7