How to find word in Excel

Question

Text in a cell in Excel is called a text string. Excel provides a number of built-in functions enabling you to select and manipulate text strings in cells. The easiest way to identify or extract a single word in a cell is to determine where the word begins and ends within the string and use the MID function to refer to the word by its location.

Table of Contents Show

Another way with COUNTIF
Other examples
Logical confusion?
Other formulas
How do you find a word in all Excel sheets?
Can Excel detect words?
What is the shortcut to search for a word in Excel?

Review the text in the cell and find the word you want to identify. For example, you might want to identify the word "good" in the following string in an Excel cell:
Now is the time for all good men to come to the aid of the party.
Start at the beginning of the string and count each character in the string until you reach the word "good." For example, "Now" starts with character 1, "time" starts at character 12 and "good" starts at character 25.
Determine how many letters the word has. For example, "good" has four letters.
Click an empty cell, such as D1, and enter the formula that identifies the word "good" within the larger string. For example, type "=MID(A1, 25, 4)" to extract the substring that begins at position 25 and has four characters
Provide the word you want to identify as part of your formula and use the FIND and MID functions to identify the word. First specify the starting position of the string, which is the result of using the FIND function on the cell. For example:
=MID(A1, FIND(A1, "good"))
Calculate the length of the word by finding the next space after the start of the word. For example:
=FIND(A1, " ", FIND(A1, "good")
Subtract the starting position of the word from the position of the space to calculate the length. For example:
=FIND(A1, " ", FIND(A1, "good")) - FIND(A1, "good")
Combine this with the MID function to identify the word with a single formula. For example:
=MID(A1, FIND(A1, "good"), FIND(A1, " ", FIND(A1, "good")) - FIND(A1, "good"))

Question: What formula tells you if A1 contains the text "apple"?

This is a surprisingly tricky problem in Excel. The "obvious" answer is to use the FIND function to "look" for the text, like this:

=FIND("apple",A1)

Then, if you want a TRUE/FALSE result, add the IF function:

=IF(FIND("apple",A1),TRUE)

This works great if "apple" is found – FIND returns a number to indicate the position, and IF calls it good and returns TRUE.

But FIND has an annoying quirk – if it doesn't find "apple", it returns the #VALUE error. This means that the formula above doesn't return FALSE when text isn't found, it returns #VALUE:

FIND returns the position of the text (if found), but #VALUE if not found.

Unfortunately, this error appears even if we wrap the FIND function in the IF function.

Grrrr. Nobody likes to see errors in their spreadsheets.

(There may be some good reason for this, but returning zero would be much nicer.)

What about the SEARCH function, which also locates the position of text? Unlike FIND, SEARCH supports wildcards, and is not case-sensitive. Maybe SEARCH returns FALSE or zero if the text isn't found?

Nope. SEARCH also returns #VALUE when the text isn't found.

So, what to do? Well, in a classic, counter-intuitive Excel move, you can trap the #VALUE error with the ISNUMBER function, like this:

=ISNUMBER(FIND("apple",A1))

Now ISNUMBER returns TRUE when FIND yields a number, and FALSE when FIND throws the error.

Another way with COUNTIF

If all that seems a little crazy, you can also the COUNTIF function to find text:

=COUNTIF(A1,"*apple*")

It might seem strange to use COUNTIF like this, since we're just counting one cell. But COUNTIF does the job well – if "apple" is found, it returns 1, if not, it returns zero.

For many situations (e.g. conditional formatting) a 1 or 0 result will be just fine. But if you want to force a TRUE/FALSE result, just wrap with IF:

=IF(COUNTIF(A1,"*apple*"),TRUE)

Now we get TRUE if "apple" is found, FALSE if not:

Note that COUNTIF supports wildcards – in fact, you must use wildcards to get the "contains" behavior, by adding an asterisk to either side of the text you're looking for. On the downside, COUNTIF isn't case-sensitive, so you'll need to use FIND if case is important.

Other examples

So what can you do with these kind of formulas? A lot!

Here are a few examples (with full explanations) to inspire you:

Count cells that contain specific text
Sum cells that contain specific text
Test a cell to see if contains one of many things
Highlight cells that contain specific text
Build a search box to highlight data (video)

Logical confusion?

If you need to brush up on how logical formulas work, see this video. It's kind of boring, but it runs through a lot of examples.

Other formulas

If you like formulas (who doesn't?!), we maintain a big list of examples.

How do you find a word in all Excel sheets?

Search in the workbook After entering the text you want to find, select Workbook in the "Within" drop-down list. Then, you can click Find Next to go through all matches, or click Find All to see all matches. You can use the keyboard shortcut Ctrl + F to open the Find and Replace box.

Can Excel detect words?

To check spelling for any text on your worksheet, click Review > Spelling. Tip: You can also press F7. Here are some things that happen when you use the spelling checker: If you select a single cell for spell check, Excel checks the entire worksheet, including the comments, page headers, footers and graphics.