Thursday, June 10, 2010

First Names and Last Names

As Excel practitioners, we are frequently presented with data that is Not in an ideal format. For instance, let’s say you are presented with list of employee names showing the Entire first and last name in each cell in a column.

As any good database manager knows, it is best to Break Down or Parse Your Information into the smallest discreet portions reasonable. To facilitate maximum information retrieval, you would naturally prefer to have the first names in one column and the last names in another column. If you have hundreds of employees in your company this would, obviously, be Pure Drudgery to convert manually.

Extracting the first names and last names into their own separate fields (columns) with Convenient Excel Formulas is a Breeze. Here is how you do it.

The Excel functions that we will be using in the formulas are:
LEN - Returns the number of characters in a text string
FIND – Returns the starting position of one text string within another text string
LEFT – Returns the specified number of characters from the start of a text string
RIGHT - Returns the specified number of characters from the end of a text string

Okay, here we go! To extract just the First Name, use the following formula:

=LEFT(A1, FIND(" ", A1, 1)-1)

To extract just the Last Name:

=RIGHT(A1, LEN(A1)-FIND(" ", A1))

These relatively simple formulas can really save your day sometime. Go ahead, give them a try!