Learn how to get the first name from a name using regular (LEFT, FIND) and user-defined functions, like GETNWORDS.
String manipulation functions and formulas are necessary to clean data in Excel. This article will demonstrate three practical ways to solve this problem.
Formula to Extract the first name from a name
Here is the 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: The FIND function first 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.
The FIND function gets the first five characters from the left.
=FIND(” “,B3,1) = 5
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”.
=LEFT(B3,FIND(” “,B3)-1)
GETNWORDS Function
The GETNWORDS function provides quick results and has easy syntax. It returns the first word from the source range using the specified delimiter, in this example, space.
Syntax and arguments:
=GETNWORDS(text, num_of_words)
- text: The text string from which you want to extract words (e.g., a full name).
- num_of_words: The number of words you want to extract from the beginning of the string
Formula:
=GETNWORDS(B4,1)
Our free Excel add-in contains useful functions that expand Excel’s built-in function library. Learn more about it.
Related Formulas: