First Names - Last Names

When importing data from sources outside of Excel, it is a common annoyance to find the imported information is not in the format you wish to have it in. For instance, as good database constructors, we know it is highly preferable to have discrete pieces of data in each field.

For example, it is better to have a field with the First Names and a second field with the Last Names of your employees rather than a single field with both. This is a common issue when importing data that is in the format, LastName, FirstName.

As we have explored previously in this blog, there are old favorites, as well as new ways of dissecting your data. For all versions of Excel you can, for instance, use the following formulas:

To extract just the First Name:
=LEFT(A1, FIND(" ", A1, 1)-1)

To extract just the Last Name:
=RIGHT(A1, LEN(A1)-FIND(" ", A1))

If you are using Excel 2013 however, you have the marvelous new Flash Fill gizmo that automatically fills your data when it senses a pattern.  This clever new tool works well with lists of names, numbers, as well as a variety other data.

A Third Way, however, (this works with all versions of Excel) is to use the familiar Find and Replace instruments. Here is how you can do this:

1.    Create/Drag two identical columns of data for the First Name and Last Name next to your imported data field as illustrated below:

 

 

2.    Then select the data in your First Name field, press Ctrl-H to pull up Find and Replace and type in asterisk-comma: ‘*,’ in the Find what textbox (see below):

 

 

3.    To find the Last Name field, simply type asterisk-space-comma: ‘, *’ in the Find what textbox

And there you have it; three excellent ways to Have it Your Way (it doesn’t only apply to hamburgers…)!