Exceljet

Quick, clean, and to the point

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 5 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 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 in the table to get the location of the space. We could use either FIND or SEARCH to do this, but I'll just use FIND because it's easier to type.

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.

This works.

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

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

We can fix that easily by subtracting one from the number we're using for num_chars.

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

Now if 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 num_chars argument.

I'll just copy the formula, paste it over the argument, and subtract 1 again, as before.

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

Course 
Core Formula

Related shortcuts

Author 
Dave Bruns