Excel: CFO.com: “The Column Name Game”


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.

Spreadsheets: The Column Name Game

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: