How to extract a first name with a helper column

In this video we'll look at how to combine the FIND function with the LEFT function to extract the first name from a full name.

Let's take a look.

Excel's LEFT and RIGHT functions are easy to use when you know how many characters you want to extract.

But what if you want to extract the first name from a full name?

If you try LEFT by itself, you'll quickly realize you have a problem because you don't know how many characters to extract.

For example, with this list of names, if I enter "5" to extract five characters, it does work for some names like Traci, and Linda, and Sarah. But "5" isn't going to work for names like Maureen, Charles, or Ann.

To make this work in a more general way, the number of characters needs to be variable and change according to each name.

The solution is to use either the FIND or SEARCH functions to get the location of the "space" character in the name. Once you have that, you can easily work out how many characters you need to extract.

To illustrate, I'll first insert a helper column into the table to get the location of the space. We could use either FIND or SEARCH to do this, but I'll just use the FIND function in this case.

FIND is case-sensitive, but it won't make any difference in this situation.

Now that we have the position, let me try the LEFT function again; this time, using the position we calculated with the FIND function, for num_chars.

As you can see, this works.

However, if I convert the names to values, you can see that we have a small problem.

Each name contains a trailing space. That's because the LEFT function is extracting each character up to, and including, the space character.

We can easily fix that by subtracting "1" from the number that FIND returns.

I'll undo the conversion to "values" and make that change.

Now when I convert to "values," you can see the extra space is gone.

Finally, let's go ahead and get rid of the helper column by moving the FIND function into the LEFT function in place of the second argument.

I'll just copy the FIND formula, paste it over the argument, and subtract "1" again, like we did before.

Now I can delete the helper column, and the first name formula works just fine.