How to search Text in Excel

Searching text in Excel is another common task that can easily be accomplished using string functions. The easiest way to search for text within a cell or string is to use the handy SEARCH function.

SEARCH takes a large string as an input (for example, the name "Sarah Smith") and searches for a smaller substring within that argument (for example, "Sarah", or "Smith"). It returns the position of the substring within the given string, so that you can tell how far into your input the item you're searching for occurs.

The syntax of SEARCH

Let's take a look at the basic formulation of the SEARCH function:

=SEARCH(find_text, within_text, start_num (optional))

That's a little bit complicated at first glance, so let's break down the formula and then take a look at a couple of examples. For now, we'll leave off the start_num argument to simplify things.

First, SEARCH will take a look at your find_text string, which it knows you want to look for within a larger string (within_text). If SEARCH finds your find_text argument within your within_text argument, it will return the position of the string in terms of a number of characters. SEARCH is case insensitive, so you won't have to worry about capital letters when you use it.

Let's take a look at a couple of examples:

=SEARCH("is", "Where is the hidden text?")
Output: 7

In the above example, Excel looks for the substring "is" within the larger string "Where is the hidden text?". It finds the string it's looking for at position 7 within the given within_text, because the phrase "is" appears seven characters into the phrase "Where is the hidden text?". Therefore, the function returns the number 7.

Here's another example:

=SEARCH("c", "Abracadabra!")
Output: 5

In this case, the function returns 5, because the letter "c" appears as the fifth characters of the phrase, "Abracadabra!".

Using the start_num argument

The third argument of SEARCH, start_num, tells the function to start a certain number of characters into the phrase in question. Any characters that appear before the start_num you specify will be ignored. Here's an example:

=SEARCH("abra", "Abracadabra")
Output: 1

The above function returns 1, because the phrase "abra" appears at the beginning of "Abracadabra". But, if we change the start_num, our results shift:

=SEARCH("abra", "Abracadabra", 2)
Output: 8

In the above example, the formula starts its search at the second characters of the phrase "Abracadabra", because the start_num is set to 2. So, it's really only searching within the phrase, "bracadabra". It returns the number 8, because the phrase "abra" is found eight spaces in to the original input phrase, "Abracadabra", when starting the search at the second character.

Searching with multiple occurences of a phrase

What happens if the phrase or letter you're searching for occurs multiple times within the string you're searching? Excel returns the position of the first occurence of your search phrase. Take, for example, the following formula:

=SEARCH("ss", "Mississippi")
Output: 3

In this case, the formula returns the number 3, because the first time the phrase "ss" appears in the word "Mississippi" starts at the third character.

What if SEARCH doesn't find what you're looking for?

If SEARCH doesn't find the phrase you're looking for, it will return a #VALUE! error. Take, for example, the following:

=SEARCH("ssss", "Mississippi")
Output: #VALUE!

There aren't four consecutive letter s's in the word "Mississippi", so the function returns an #VALUE! error.

Whenever you're using the SEARCH function, be sure to check the output to ensure that what you're looking for was found. Otherwise, your formulas might break!

SEARCH vs. FIND: case sensitivity

As mentioned above, the SEARCH function is case insensitive — you don't have to worry about capitalization when using it. There is a similar function, called FIND, that is case sensitive — meaning that it treats capital and lower case letters as if they are different. Take a look at the following examples, which demonstrates the difference between the two functions:

=SEARCH("cap", "CaPiTaL")
Output: 1

=FIND("cap", "CaPiTaL")
Output: #VALUE!

Using wildcards

You can also use wildcards, which are variable characters that represent one or more of any character on the keyboard, within your find_text argument. Take a look at the below for a few examples:

=SEARCH("c*b", "Abracadabra")
Output: 5

=SEARCH("c?d", "Abracadabra")
Output: 5

=SEARCH("rac*", "Abracadabra")
Output: 3

=SEARCH("a???cad", "Abracadabra")
Output: 1

For more information on wildcards, check out our tutorial on wildcards in Excel.

Comments