Excel FIND and SEARCH functions with formula examples

Excel FIND function - things to remember!

To correctly use a FIND formula in Excel, keep in mind the following simple facts:

1. The FIND function is case sensitive. If you are looking for a case-insensitive match, use the SEARCH function.
2. The FIND function in Excel does not allow using wildcard characters.
3. If the find_text argument contains several characters, the FIND function returns the position of the first character. For example, the formula FIND("ap","happy") returns 2 because "a" in the 2^nd letter in the word "happy".
4. If within_text contains several occurrences of find_text, the first occurrence is returned. For example, FIND("l", "hello") returns 3, which is the position of the first "l" character in the word "hello".
5. If find_text is an empty string "", the Excel FIND formula returns the first character in the search string.
6. The Excel FIND function returns the #VALUE! error if any of the following occurs:
   • Find_text does not exist in within_text.
   • Start_num contains more characters than within_text.
   • Start_num is 0 (zero) or a negative number.

1. Case-sensitive FIND vs. case-insensitive SEARCH

The most essential difference between the Excel SEARCH and FIND functions is that SEARCH is case-insensitive, while FIND is case-sensitive.

For example, SEARCH("e", "Excel") returns 1 because it ignores the case of "E", while FIND("e", "Excel") returns 4 because it minds the case.

2. Search with wildcard characters

Unlike FIND, the Excel SEARCH function accepts wildcard characters in the find_text argument:

• A question mark (?) matches one character, and
• An asterisk (*) matches any series of characters.

To see how it works on real data, consider the following example:

As you see in the screenshot above, the formula SEARCH("function*2013", A2) returns the position of the first character ("f") in the substring if the text string referred to in the within_text argument contains both "function" and "2013", no matter how many other characters there are in between.

Example 1. Find a string preceding or following a given character

This example shows how you can find and extract all characters in a text string to the left or to the right of a specific character. To make things easier to understand, consider the following example.

Supposing you have a column of names (column A) and you want to pull the First name and Last name into separate columns.

To get the first name, you can use FIND (or SEARCH) in conjunction with the LEFT function:

=LEFT(A2, FIND(" ", A2)-1)

or

=LEFT(A2, SEARCH(" ", A2)-1)

As you probably know, the Excel LEFT function returns the specified number of left-most characters in a string. And you use the FIND function to determine the position of a space (" ") to let the LEFT function know how many characters to extract. At that, you subtract 1 from the space's position because you don't want the returned value to include the space.

To extract the last name, use the combination of the RIGHT, FIND / SEARCH and LEN functions. The LEN function is needed to get the total number of characters in the string, from which you subtract the position of the space:

=RIGHT(A2,LEN(A2)-FIND(" ",A2))

or

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2))

The following screenshot demonstrates the result:

For more complex scenarios, such as extracting a middle name or splitting names with suffixes, please see How to split cells in Excel using formulas.

Example 2. Find Nth occurrence of a given character in a text string

Supposing you have some text strings in column A, say a list of SKUs, and you want to find the position of the 2^nd dash in a string. The following formula works a treat:

=FIND("-", A2, FIND("-",A2)+1)

The first two arguments are easy to interpret: locate a dash ("-") in cell A2. In the third argument (start_num), you embed another FIND function that tells Excel to start searching beginning with the character that comes right after the first occurrence of dash (FIND("-",A2)+1).

To return the position of the 3^rd occurrence, you embed the above formula in the start_num argument of another FIND function and add 2 to the returned value:

=FIND("-",A2, FIND("-", A2, FIND("-",A2)+1) +2)

Another and probably a simpler way of finding the Nth occurrence of a given character is using the Excel FIND function in combination with CHAR and SUBSTITUTE:

=FIND(CHAR(1),SUBSTITUTE(A2,"-",CHAR(1),3))

Where "-" is the character in question and "3" is the Nth occurrence you want to find.

In the above formula, the SUBSTITUTE function replaces the 3rd occurrence of dash ("-") with CHAR(1), which is the unprintable "Start of Heading" character in the ASCII system. Instead of CHAR(1) you can use any other unprintable character from 1 to 31. And then, the FIND function returns the position of that character in the text string. So, the general formula is as follows:

At first sight, it may seem that the above formulas have little practical value, but the next example will show how useful they are in solving real tasks.

Example 3. Extract N characters following a certain character

To locate a substring of a given length within any text string, use Excel FIND or Excel SEARCH in combination with the MID function. The following example demonstrates how you can use such formulas in practice.

In our list of SKUs, supposing you want to find the first 3 characters following the first dash and pull them in another column.

If the group of characters preceding the first dash always contains the same number of items (e.g. 2 chars) this would be a trivial task. You could use the MID function to return 3 characters from a string, starting at position 4 (skipping the first 2 characters and a dash):

=MID(A2, 4, 3)

Translated into English, the formula says: "Look in cell A2, begin extracting from character 4, and return 3 characters".

However, in real-life worksheets, the substring you need to extract could start anywhere within the text string. In our example, you may not know how many characters precede the first dash. To cope with this challenge, use the FIND function to determine the starting point of the substring that you want to retrieve.

The FIND formula to return the position of the 1^st dash is as follows:

=FIND("-",A2)

Because you want to start with the character that follows the dash, add 1 to the returned value and embed the above function in the second argument (start_num) of the MID function:

=MID(A2, FIND("-",A2)+1, 3)

In this scenario, the Excel SEARCH function works equally well:

=MID(A2, SEARCH("-",A2)+1, 3)

It's great, but what if the group of chars following the first dash contains a different number of characters? Hmm... this might be a problem:

As you see in the above screenshot, the formula works perfectly for rows 1 and 2. In rows 4 and 5, the second group contains 4 characters, but only the first 3 chars are returned. In rows 6 and 7, there are only 2 characters in the second group, and therefore our Excel Search formula returns a dash following them.

If you wanted to return all chars between the 1^st and 2^nd occurrences of a certain character (dash in this example), how would you proceed? Here is the answer:

=MID(A2, FIND("-",A2)+1, FIND("-", A2, FIND("-",A2)+1) - FIND("-",A2)-1)

For better understanding of this MID formula, let's examine its arguments one by one:

• 1^st argument (text). It's the text string containing the characters you want to extract, cell A2 in this example.
• 2^nd argument (start_position). Specifies the position of the first character you want to extract. You use the FIND function to locate the first dash in the string and add 1 to that value because you want to start with the character that follows the dash: FIND("-",A2)+1.
• 3^rd argument (num_chars). Specifies the number of characters you want to return. In our formula, this is the trickiest part. You use two FIND (or SEARCH) functions, one determines the position of the first dash: FIND("-",A2). And the other returns the position of the second dash: FIND("-", A2, FIND("-",A2)+1). Then you subtract the former from the latter, and then subtract 1 because you don't want to include either dash. As the result, you will get the number of characters between the 1^st and 2^nd dashes, which is exactly what we are looking for. So, you feed that value to the num_chars argument of the MID function.

In a similar fashion, you can return 3 characters after the 2^nd dash:

=MID(A2, FIND("-",A2, FIND("-", A2, FIND("-",A2)+1) +2), 3)

Or, extract all the characters between the 2^nd and 3^rd dashes:

=MID(A2, FIND("-", A2, FIND("-",A2)+1)+1, FIND("-",A2, FIND("-", A2, FIND("-",A2)+1) +2) - FIND("-", A2, FIND("-",A2)+1)-1)

Example 4. Find text between parentheses

Supposing you have some long text string in column A and you want to find and extract only the text enclosed in (parentheses).

To do this, you would need the MID function to return the desired number of characters from a string, and either Excel FIND or SEARCH function to determine where to start and how many characters to extract.

=MID(A2,SEARCH("(",A2)+1, SEARCH(")",A2)-SEARCH("(",A2)-1)

The logic of this formula is similar to the ones we discussed in the previous example. And again, the most complex part is the last argument that tells the formula how many characters to return. That pretty long expression in the num_chars argument does the following:

• First, you find the position of the closing parenthesis: SEARCH(")",A2)
• After that you locate the position of the opening parenthesis: SEARCH("(",A2)
• And then, you calculate the difference between the positions of the closing and opening parentheses and subtract 1 from that number, because you don't want either parenthesis in the result: SEARCH(")",A2)-SEARCH("(",A2))-1

Naturally, nothing prevents you from using the Excel FIND function instead of SEARCH, because case-sensitivity or case-insensitivity makes no difference in this example.