How to extract the first word from some text? We will show you various solutions using Excel’s FIND, LEFT, and ExtractWord functions.
Today’s tutorial is a part of our definitive guide on Excel Formulas.
Generic formula to get the first word
First, place the initial data set. The main point of the formula is the following:
The FIND function locates and gets the position of the first occurrence of a space (“ “) character in the given text string. The inner part of the formula returns the position as a number.
Finally, starting from the first character, the LEFT function extracts the characters until the length of the first word – 1. We have to cut the last character because it’s the space.
In the 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. Using the formula mentioned above, we’ll get a #VALUE! Error. It is important to prevent formula errors use the IFERROR function.
In the example:
=IFERROR(LEFT(B3,FIND(" ",B3)-1),B3) or =IFERROR(LEFT(B3,FIND(" ",B3)-1),”The cell contains one word”)
In this case, the formula returns the first word:
When 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(B3,FIND(" ",B3&" ")-1)
Known limitations: The above-demonstrated formula works only with the first space.
Get the first word using a user-defined function
If you want to combine more than one built-in Excel into a formula, be careful; sometimes, it is not an easy task. In the example, we will use the ExtractNthWord user-defined function.
- text: the text string that we want to modify
- n: the number of the word in the entire text
The solution looks like this: