[no_toc] Learn how the get the first name from a name using regular (LEFT, FIND) and user-defined (TextLeft, ExtractWord) functions. This is a common occurrence when you have to clean data in Excel. We’ll explain three effective ways to solve this problem.
Generic Formula to Extract the first name from a name
Example: Use the FIND and LEFT functions
In this case, strings are separated with space. Our goal is to find the first space and extract the first name from left.
Apply the combination of the FIND and LEFT functions to extract the first name (without additional spaces or other delimiters) from a full name.
In our example, the cell B2 contains the following formula:
Explanation on how this formula works
As first, the FIND function locates the first space character(“ “) in cell A2. After that, it returns the actual position of the first space in the given cell or string.
The result is 1. The LEFT function uses this result as a parameter. In the example, the position of the first separator (space) is 5.
=FIND(" ",A2,1) = 5
The FIND function gets the first 5 characters from the left.
The LEFT function helps us to get the first name because it will get the first string from left until the first space. We have to cut the space character (apply “-1” in the formula), so the result is “John”.
TextLeft and ExtractNthWord functions
Our free excel add-in DataFx contains all the explained functions.
Get the first name using UDFs
The TextLeft function provides a quick result and it has an easy syntax. The function returns the first word from the source range using the specified delimiter, in the example, space.
We only have to add the cell reference and space as the second parameter.
Apply the ExtractNthWord function
This UDF will return with the first word from a string.
As a result of
we’ll get the same result, “John”
Handling unusual prefixes
If you have strings with custom prefixes (Dr. Mr., etc) we have to remove them first.
- Remove first character
- Extract characters from right
- Get last name
- Extract middle name
- Join first and last name