How to find text with a formula

When you're working with text, you often need to pinpoint the location of some bit of text inside another. You can then use this position to extract or replace the text.

In this video we'll look at how to locate the position of one text string inside another.

Let's take a look.

Excel contains two functions that can help you locate the position of text inside other text. The first function is FIND.

FIND takes three arguments: the text you're looking for, the text you're looking within, and an optional argument which indicates the starting point for the search. This argument defaults to "1" if you don't supply your own value.

Note that FIND is case sensitive.

Now let's look at how FIND works using the values in this table.

Column B contains the text we're looking for, Column C contains the text we're looking within, and column D contains the start number. In Column E, I'll add the FIND function and configure it to use these values.

The first result is "1" because a capital "A" is the first character in Apple.

We get a #VALUE error in the next example because there is no lowercase "a."

When we look for the letter "p" FIND returns "2". That's because FIND gets the position of the first occurrence when there is more than one.

If you're searching for multiple characters, you'll get the position of the first character. For the letters "le" this is "4".

Since FIND is case-sensitive, we'll get "12" if we look for "the" with a lowercase "t." Switching to an uppercase "T" will return "1".

When you just want a simple test, instead of a position, you can wrap the FIND function in a function called ISNUMBER. The ISNUMBER function will return "TRUE" when FIND returns a number, and "FALSE" if not.

On the next sheet, we have examples for the SEARCH function. SEARCH is very similar to FIND and uses exactly the same arguments. However, SEARCH is not case-sensitive and also supports "wildcards."

When looking for the letter "a" SEARCH returns "1" for both a lower case and uppercase "A."

Like FIND, SEARCH returns #VALUE if the text is not found.

A lowercase "the" returns "1". If you want to find the second occurrence of "the," you could use a start number of "4" which effectively skips the first "the."

Finally, SEARCH supports "wildcards." You can use a "?" to represent one character. So, "?at" returns "5" because it matches "cat."

You can use an asterisk (*) to match one or more characters.

So, "20*" matches "2010" and returns "7".