CFO.com yesterday shared a wonderful step-by-step solution to a more-common-than-you’d-think spreadsheet problem. A reader asked this 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)?
I get this sort of question all of the time from my colleagues, and the solution presented in the article is precisely the method I use. I recommend that all Excel users give this solution a try, as it teaches some very good fundamentals of text manipulation and data processing.
An example of the methodology:
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.