How to extract the first word from some text? We will show you various solutions using the FIND, LEFT, and ExtractWord function in Excel.
Generic formula to get the first word
Explanation of the regular formula
As first, place the initial data set. The main point of the formula the following:
The FIND function locates and gets the position of the first occurrence of a space (“ “) character in the given cell (text).
The formula returns the position as a number.
After that, – starting from the first character of the string – the LEFT function extract the characters until the length of the first word – 1. We have to cut the last character because it’s the space.
In today’s example, the formula looks like this:
How to get the first word if the cell contains only one word?
Error handling is important if you want to show that something went wrong.
If we use the above-mentioned formula, we’ll get a #VALUE! error. It is important to prevent formula errors use the IFERROR function.
In the example:
=IFERROR(LEFT(A2,FIND(" ",A2)-1),”The cell contains one word”)
In this case, we’ll get the first the word:
If an error occurs, the IFERROR expression returns with a user-specified message or the first word. Furthermore, it’s a smart way to handle the error: Add additional space to the cell value before running the FIND function.
=LEFT(A2,FIND(" ",A2&" ")-1)
The above-demonstrated formula works only with the first space.
Get first word using a user-defined function
Customization of built-in Excel formulas is not an easy task. But we’ll try it. Now we’ll introduce the ExtractNthWord function!
The solution looks like this: