Learn how the get the first name from a name using regular (LEFT, FIND) and user-defined (TextLeft, ExtractWord) functions.
String manipulation functions and formulas are necessary if you want to clean data in Excel. This article will demonstrate three practical ways to solve this problem.
Generic Formula to Extract the first name from a name
Formula to strip the first name from a text:
=LEFT(full_name,FIND(" ",full_name)-1)In the example, we use the FIND and LEFT functions. In this case, strings are separated with space. Therefore, we aim to find the first space and extract the first name from the left.
Combine the FIND and LEFT functions to extract the first name (without additional spaces or other delimiters) from a full name.
In our example, cell D3 contains the following formula:
=LEFT(B3,FIND(" ",B3)-1)
Explanation
At first, the FIND function locates the first space character(“ “) in cell B3. 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(" ",B3,1) = 5The FIND function gets the first five characters from the left.
=LEFT(B3,FIND(" ",B3)-1)The LEFT function helps us to get the first name because it will get the first string from the 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 contains useful functions that expand Excel’s built-in function library. Learn more about it.
Get the first name using a UDF (user-defined function)
The TextLeft function provides a quick result and 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 argument.
=TextLeft(B3," ")Apply the ExtractNthWord function
The function will return with the first word from a string.
=Extract_Nth_Word(B3,1)The result is “John”.
Here is the workaround with unusual prefixes: If you have strings with custom prefixes (Dr. Mr., etc.), we must remove them first.
Related Formulas:
- Remove the first character
- Extract characters from right
- Get the last name
- Extract middle name
- Join first and last name
Stay tuned.
