**How To Use Excel Functions**

SEARCH And FIND

SEARCH And FIND

*Written by co-founder Kasper Langmann, Microsoft Office Specialist.*

In this tutorial, we will be discussing the ‘SEARCH’ and ‘FIND’ functions in Microsoft Excel.

**At the surface, these 2 functions seem simple…**

…However, they have *extremely* powerful potential when used together with other functions (as you’re about to learn).

The key to leveraging the power of ‘SEARCH’ and ‘FIND’ really lies in your ability to think analytically.This has a lot to do with building on a growing and comprehensive knowledge of other Excel functions

So, let’s get started!

**‘SEARCH’ vs. ‘FIND’**

While ‘SEARCH’ and ‘FIND’ might *sound* like the same thing…

… these 2 functions are quite different!

**There are 2 notable differences between them:**

**1:** While we can use wildcards with ‘SEARCH’ – we *cannot* with ‘FIND’.

**2:** When using ‘FIND’ letter case matters. When using ‘SEARCH’ it *does not*. If we attempt to find a character of the wrong case with the ‘FIND’ function, it will return the ‘#VALUE!’ error.

On the surface, there’s really nothing too glamorous about these functions.

But, as we unwrap the possibilities by combining them with other functions, you will see how powerful and useful they are.

In our first example, we have a table of string values.

**For each, we will compare the use of ‘FIND’ and ‘SEARCH’ side by side.**

On the first row of our table, we have the string value ‘Tarantula’. We’re going to use ‘A’ as our ‘find_text’ argument in both functions.

Also, we are omitting the ‘start_num’ argument.

Recall that the ‘FIND’ function is a case sensitive function when dealing with letters. That causes the ‘#VALUE!’ error in this case.

Since the ‘SEARCH’ function is *not* sensitive to letter case, it returns ‘2’. This is the position of the first ‘A’ that occurs in the ‘within_text’ string.

On the next row, we change things up a bit with the exact same string.

**This time we use ‘a’ instead as our ‘find_text’ argument. **

We also use a ‘start_num’ of 5 to find the position of the next ‘a’ beyond that point.

This time the ‘FIND’ function and the ‘SEARCH’ function return the same result of ‘9’.

Since the 5^{th} position in the string ‘Tara**n**tula’ is the letter ‘n’, the functions will find the last ‘a’ in the string (which is the 9^{th} position).

A similar example using the text string ‘Loops’ on the next two rows drives the point of the previous examples home.

In the next example, we look at using wildcards with our functions.

**Recall that we cannot use wildcards with ‘FIND’ so it returns the ‘#VALUE!’ error in this case.**

We use ‘a*n’ as our ‘find_text’ argument in the ‘SEARCH’ function.

This means that we are searching for a string of any number of characters (‘*’) that begins with ‘a’ and ends with ‘n’.

On row 9 we have selected ‘2’ as our ‘start_num’ argument which means the formula will return a value of ‘10’.

This is the position of the ‘a’ in the word ‘train’. This is the first string of characters beyond the second position of the original string that begins with ‘a’ and ends with ‘n’ (‘tr** a**i

**’).**

__n__**The result of our previous formula resulted in ‘10’.**

Let’s now use that as our ‘start_num’ argument on the same original string.

That way we can see if there is another substring that starts with ‘a’ and ends with ‘n’.

On row 10, we see that in fact there is another substring that ‘SEARCH’ locates based on the wildcard ‘a*n’.

It begins at position 16 of the original text with the ‘a’ in the word ‘st** a**tio

**’.**

__n__**Now let’s look at using the ‘?’ wildcard for single characters.**

On row 14, we select ‘?t’ as our ‘find_text’ argument.

This means we want ‘SEARCH’ to locate an instance of the letter ‘t’ preceded by any single character.

In our formula, we will omit the ‘start_num’ argument. **That way it searches for the first instance of our ‘find_text’.**

In this case, since the first ‘t’ is in the first word, ‘At’, the result of our formula is ‘1’.

Notice that the position of the ‘t’ in ‘At’ is actually ‘2’.

Since our ‘find_text’ is ‘?t’, our ‘SEARCH’ function actually returns the position of the ‘?’ wildcard character.

For the sake of contrast, let’s change the ‘find_text’ argument to ‘t?’ on row 15 and add a ‘start_num’ of ‘3’.

Notice in the figure above that the formula returns the value ‘4’.

**Why?**

Because the first instance of ‘t’ with any single character to the right AFTER position 3 in the original string is ‘t’ in the word ‘the’.

The main idea with ‘FIND’ and ‘SEARCH’ is that the numeric values they return are positions within the selection being specified as the ‘within_text’.

This is *extremely* valuable when used within other functions…

There are many ways to separate a lengthy text string in Excel…

… Especially in a situation where we’d like to separate a full name string into separate first and last name text strings.

Let’s see the value of the ‘FIND’ function combined with the ‘LEFT’, ‘RIGHT’, and ‘LEN’ functions. In case you’re not familiar with these functions, let’s take a quick look…

The ‘LEFT’ and ‘RIGHT’ functions return a substring of text based on the number of characters we specify from the left or right, respectively.

**The syntax for both requires two arguments:**

**‘text’**which is either a literal string or a cell reference to a cell containing a string.**‘num_chars’**which is simply the number of characters from either the left or right.

‘=RIGHT(text, [num_chars])’

‘=LEFT(text, [num_chars])’

*Theoretically*, the ‘num_chars’ argument is optional. But we will be using it in our scenarios.

**So, the result of ‘=RIGHT(“Saturday”,3)’ would be ‘day’ while the result of ‘=LEFT(“Sunday”,3)’ would be ‘Sun’.**

The ‘LEN’ function returns the length of a text string and its only parameter is ‘text’. Like with all the functions we are discussing, the ‘text’ argument can be a *literal string* or a *cell reference* of the cell that contains a string.

‘=LEN(text)’

The result of ‘=LEN(“length”)’ would be ‘6’ since there are six characters or letters in the text string ‘length’.

**A word of caution when using the ‘LEN’ function: beware of spaces.**

Spaces are considered characters within a string.This is especially important to be aware of in our example. Here, we’re splitting full name strings into separate first and last name substrings. These contain spaces, that we should mindful of.

**NOTE: Since our ‘find_text’ argument is the space between first and last name, our ‘FIND’ function is interchangeable with the ‘SEARCH’ function. **

Either function will achieve the same outcome here.

We now need to set up our formula to return the position of the space between the first and last name.

This is simply ‘=FIND(“ “,A2)’ where ‘A2’ is the cell reference that contains ‘Ryan Thompson’.

Notice in column B, the result is 5. Since ‘Ryan’ is four letters, clearly this is the correct result.

**Now we will get creative with that result by subtracting ‘1’ from it.**

Notice that in column C we now have that result.

We do this with a simple formula that subtracts ‘1’ from the cell reference of our previous formula’s result, or ‘=B2-1’.

**But, we could also combine this into our original formula like ‘=FIND(“ “,A1)-1’.**

The next step is to extract the first name only from our original string…

And that’s super easy!

**Why?**

Because you’ve just figured out how to get the number of characters prior to the space between the first and last name.

We can do this using the ‘LEFT’ function and the result of our previous formula where we subtracted ‘1’ from our ‘FIND’ function.

This will simply be ‘=LEFT(A2,4)’.

We could also write the formula replacing the ‘num_chars’ argument with the cell reference ‘C2’ since it contains the result we need.

**See the figure below to review the ways in which we can build our formulas to extract the first and last names from our original string.**

We will start by parsing out the first name from the original text in column A on row 2.

Row 3 shows the formula that we build for each step in the progression to our result in columns B through D.

For the first name, we progress from getting the position of the space between names using the ‘FIND’ function. This is shown in column B (‘FIND result’).

Our *next step* is to get the length of the first name by subtracting ‘1’ from the ‘FIND’ result.

**This method of finding the length of the first name will always hold true since the ‘FIND’ function result is based on locating the position of the space after the first name.**

That value is listed in column C (‘FIND result – 1’).

**The final step** in the progression to building our formula to extract the first name is shown in column D (‘First Name’).

We do this with the ‘LEFT’ function, using the results of our previous formula for the ‘num_chars’ argument.

**So, as we can see, without the result of the ‘FIND’ function, the ‘LEFT’ function is not useful. **

To extract the last name from our original string we now turn to the ‘LEN’ function. Here we simply use its result when applied to the original string and subtract the result of our original ‘FIND’ formula.

We know the number position of the space between names is ‘5’.

If we subtract that from the length of the entire string, we get the length of the last name.

Then all we need to do is insert this as our ‘num_chars’ argument in the ‘RIGHT’ function.

That’s it. *(See cell F3 in the above figure to see how simple this is.)*

**The figure below** shows this same process of building our formulas without using cell references in substitution of our ‘FIND’ and ‘LEN’ function on row 5.

By comparing to our formulas on row 3, we can see how easy it can be to build complex functions to get the job done.

The best method while learning is to simply break down the process in steps like we have done.

We can see how fundamental the results of ‘FIND’ can be to a larger and more complex formula to achieve solutions to problems.

It is amazing to see how such a simple function can become so powerful when combined with others!

This is one more case where a very simple function (or two) that Excel offers to tell us something very basic can be leveraged to provide elegant solutions.

The simple results to be gotten from ‘SEARCH’ and ‘FIND’ clearly have the potential to provide great fuel for solutions to many different problems.

The power of these two very simple functions is only limited by the ability to be creative and analytical in approaching those problems needing solutions.