In this Article
This tutorial will demonstrate how to extract the first word from a cell in Excel & Google Sheets.
Extract First Word From a Text
=LEFT(B3,FIND(" ", B3)-1)
Let’s delve into the more details of the functions used in the above formula.
The FIND Function
We used the FIND function to get the position of the first occurrence of space in the text.
Because the FIND function includes the position of the space character from the text in the calculation, we’ll deduct 1 from the value returned (from the FIND function) to exclude the space character.
The LEFT Function
The LEFT Function will extract a certain number of characters from the string, starting from the left. The number of characters was determined in the previous formula.
In case the cell has only one word, the formula will return #VALUE error.
So to prevent the formula to return the error we can use the following two options using:
The IFFERROR Function
We can solve this issue by using the IFERROR function before our formula, like this:
The IFERROR function, when finds an error will return the original text value – which could be the one word or an empty cell.
There’s also a smart and simpler way to handle the #VALUE error. It is to add additional space to the second argument of the FIND function, with ampersand operator, like this:
=LEFT(B3,FIND(" ",B3&" ")-1)
Now the FIND function will always find a space because we’ve appended a space to the end of each string of text.
If the text string is not separated by space but from a different delimiting character, then by simply changing the value of the first argument in the FIND function with that delimiting character, we’ll get the first word from the text.
Get Last Word
Extracting the last word from a string of text is more complicated. One option is to use a formula like this:
=TRIM(RIGHT(SUBSTITUTE(B3," ",REPT(" ",LEN(B3))),LEN(B3)))
Extract First Word In Google Sheets
The formula to extract the first word from a text works exactly the same in Google Sheets as in Excel: