In Excel you commonly deal with cells that contain strings of text of varying length, from single words to sentences or even paragraphs. While there's not a built in function to retrieve certain words from within a larger body of text, it's possible to combine functions in Excel to write a formula to extract the first word from text.
For example, say you had this spreadsheet of president names, and you want to write an Excel formula to return only the first name of each president.
| A | |
|---|---|
| 1 | Joe Biden |
| 2 | Donald Trump |
| 3 | Barack Obama |
| 4 | George W. Bush |
We will write this formula in multiple steps, by first identifying the functions we need, and then combining them into a formula to retrieve the first word.
The first Excel function we need to know is the LEFT function. The LEFT function in Excel extracts the left-most text of a certain length from a cell that contains text. This is necessary to extract the first word from a sentence because the first word is the left-most text of a sentence.
The LEFT function works like this:
=LEFT(text, [number_of_characters])In Excel, function inputs that go inside the parenthesis are called arguments, and the LEFT function has two (though one is optional).
The text argument is easy; this is the text or sentence you want to extract the first word from. In the case of Joe Biden in the example above, this argument would be A1. This can either be a string of text or a reference to a cell which contains text.
The number_of_characters argument is more tricky. It tells the LEFT function the length of text you want to return from the beginning of the text argument. If you use a 3 for this argument it would return the 3 left-most characters of the text.
Joe Biden123456789You could use 3 and write =LEFT(A1, 3) and get "Joe" for the first example, but this approach won't work for the others. It would return "Don" from Donald Trump, "Bar" from Barack Obama, and so on.
Donald Trump123Barack Obama123How can you determine what to use for the number_of_characters argument so that the same formula works for every example?
That's where the FIND function comes in.
The FIND function in Excel is the missing piece of the puzzle when writing a formula to extract the first word of a sentence. It returns the position of the character or text you are searching for in a longer string of text. When extracting the first word of a sentence or text, you need to know the position of the first space, because this separates the first word from the rest of the text.
The FIND function works like this:
=FIND(character, text)The character argument is the character you are looking for, in quotation marks. In our example, it would simply be a space.
The text argument is the text or cell that contains the text you want to search for the character. This is the same as the text argument we used for the LEFT function.
In the case of Joe Biden, we would write:
=FIND(" ", A1)
This formula would return 4, which is the position of the space in the text contained in cell A1.
Joe Biden123456789But what if there are two spaces? Which position will the FIND function return? The FIND function looks from left to right, and by default, will return the position of the first matching character it finds. So in the case of "George W. Bush" which contains two spaces, the FIND function will return the position of the first space, which is what we want.
Now that we know how to use the LEFT function to retrieve the farthest left text from a cell, and we know how to use the FIND function to get the position of the first space in text, we can combine them to write a formula that extracts the first word from any text.
The following formula will work to extract the first word from any text in Excel (of course, you need to replace A1 with whichever cell contains the text):
=LEFT(A1, FIND(" ", A1)-1)In the example of the text in cell A1, we use FIND(" ", A1) to retrieve the position of the first space in the text, which is 4.
Because we don't want to include the space in the text we extract, we subtract one, which gives us 3. We then use the LEFT function to get the left-most text of length 3, which happens to be "Joe" (the first word of the text)!
=LEFT(A2, FIND(" ", A2)-1)In the example of the text in cell A2, we use FIND(" ", A2) to retrieve the position of the first space in the text, which is 7.
Because we don't want to include the space in the text we extract, we again subtract one, which gives us 6. We then use the LEFT function to get the 6 letters at the beginning of the text, which is "Donald".
The #VALUE! error in Excel is very broad, but generally means that there is something wrong with your formula. If the formula explained above is returning this error, then it is likely because there is no space in the text it is referencing because there is only one word in the cell.
The formula explained above works fine when the target text contains multiple words, but will return the #VALUE! error if there is no space for the FIND function to find. Take this example of city names below:
| A | |
|---|---|
| 1 | Baton Rouge |
| 2 | San Antonio |
| 3 | Los Angeles |
| 4 | Austin |
The formula will work just fine on the first three cells, because they each contain multiple words, so they also contain a space. But the text "Austin" doesn't contain a space, so the formula will return the #VALUE! error.
Luckily, Excel gives us a way around this error: the IFERROR function.
The IFERROR function in Excel lets us write a formula, and then return something else if our formula results in an error. It looks like this:
=IFERROR(try_this, do_this_if_error)The first argument, try_this, is the formula we want to try.
The second argument, do_this_if_error, is the value we want to return if the try_this formula results in an error.
The formula outlined above works fine when there are multiple words in the cell, but returns an error when there is only one word in the cell. If there is only one word in the cell, then that word is the first word anyway so we can just return the contents of that cell. Therefore, we can simply enclose our formula in the IFERROR function and have it return the contents of the cell if there is an error.
The following formula is the error-proof way to extract the first word from text in Excel:
=IFERROR(LEFT(A1, FIND(" ", A1)-1), A1)This formula tells Excel to return the left-most text up to the first space, unless there's an error (likely because there is no space), in which case simply return the entire contents of the cell.
Like any concept in Excel, this type of text manipulation takes practice and the best way to understand it is to get your hands dirty and try some practice exercises.