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.
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.
The Excel SEARCH function returns the location of one text string inside another. SEARCH returns the position of the first character of find_text inside within_text. Unlike FIND, SEARCH allows wildcards, and is not case-sensitive.