To extract the first word from some text in Excel, you can use a formula with built-in functions like FIND and LEFT.
Today’s tutorial is a part of our definitive guide on Excel Formulas.
How to extract the first word from text in Excel?
Here are the steps to get the first word from the text:
- Open Excel
- Type =LEFT(text,FIND(” “,text)-1)
- Press Enter.
- The formula will extract the first word.
Explanation
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:
=LEFT(B3,FIND(” “,B3)-1)
How do you get the first word if the cell contains only one word? Error handling is important to show that something went wrong. Using the formula mentioned above, we’ll get a #VALUE! Error. It is important to prevent formula errors by using the IFERROR function.
=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:
=IFERROR(LEFT(B3,FIND(” “,B3)-1),B3)
Explanation: 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 an extra space to the cell value before running the FIND function.
=LEFT(B3,FIND(” “,B3&” “)-1)
Known limitation: 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 easy. In the example, we will use the GETWORDS user-defined function.
Syntax:
=GETWORDS(text, n, delimiter)
- text: cell reference
- n: position
- delimiter: define a separator
The solution looks like this:
=GETWORDS(B5,1,” “)
We strongly recommend using our free Excel add-ins if you have to clean data using Excel. In addition, the productivity suite contains a custom function library.