SEARCH vs FIND Function in Excel

When working with text data in Microsoft Excel, you often need to locate specific characters or substrates within a larger string.

In such situations, Excel offers two useful functions: SEARCH and FIND.

These two functions, while seemingly similar, have some key differences that affect how they operate.

You will find that both the SEARCH and FIND Functions are immensely useful, when used in combination with other text functions.

In this tutorial, we will go over what the two functions do, as well as the similarities and differences between them.

So let’s get started.

Excel SEARCH Function in Context

The SEARCH Function can be used to locate a specified character or substring, within a greater text string. It returns the position of the character or substring. The position is given by a number.

So you can search for single characters within a greater text string, or whole words in text strings.

Let’s take a closer look at the syntax of the SEARCH Function.

=SEARCH(find_text, within_text,[start_num]) where:

• find_text refers to the character or substring that you want to locate. This is a required value.
• within_text refers to the greater text string that needs to be searched and evaluated. This is a required value.
• start_num refers to the character number, within the greater text string that you would like to start searching from. This is an optional value. If omitted, this value is 1.

Excel FIND Function in Context

The FIND Function is also used to locate a specified delimiter or substring within a greater text string. It also returns the position of the character or substring. The position is given by a number.

So, as with the SEARCH Function, you can search for single characters within a greater text string, or whole words in text strings.

Let’s review the syntax of the FIND Function and the arguments needed.

=FIND(find_text, within_text, [start_num]) where:

• find_text refers to the character or substring, within the greater text string that you would like to find. This is a required value.
• within_text refers to the text string that needs to be searched and evaluated. This is a required value.
• start_num refers to the character number, within the greater text string that you would like to start searching from. This is an optional value. If omitted this value is 1.

We will take a look at a few examples to get a better understanding of how these functions work.

SEARCH Function vs FIND Function

Let’s go through some examples that will help you understand how FIND and SEARCH are similar and what are the differences.

Below, we have single characters and substrings in column A. We would like to find the position of these characters and substrings within the greater text strings from column B.

Column C will contain the results returned by the SEARCH Function whereas column D will be populated by the results from the FIND Function.

So, we will clearly see in which cases the SEARCH and FIND Functions can be used interchangeably and where they differ.

Example 1 – Finding Character in String

In our first example, we want to find the position of the single character “a” within the greater text string “abcdef”.

These are our steps.

1. We will enter the following formula in cell C2.

=SEARCH(A2,B2)

2. We see that the value of 1 is returned by the SEARCH Function, since “a” is the first character in the greater text string.

3. Now we will use the FIND Function to locate “a”, within the greater text string. So, insert the following formula in cell D2.

=FIND(A2,B2)

Discussion

We can see that for a simple, single character based search, we get the same result whether we use the SEARCH or FIND Function.

Both functions return 1, which means the “a” is found in the first position of the greater text string.

Also read: Find Last Occurrence of a Value in a Column in Excel

Example 2 – Finding Substring in a String

In this example, we want to find the position of the word “form” in the text “fullplatform”.

 These are our steps.

1. We will enter the following formula in cell C3.

=SEARCH(A3,B3)

The SEARCH Function returns a value of 9. The word “form” starts at the 9th character of the word “fullplatform”.

2. Now we will use the FIND Function to locate the word “form”, within the greater text string. So, insert the following formula in cell D3.

=FIND(A3,B3)

Discussion

Again, we can see that in this case, we get the same result whether we use the SEARCH or FIND Function. Both functions return 9, since the word “form” starts at the 9th position in the greater text string.

Also read: Find the Position of a Character in a String in Excel

Example 3 – Finding First Instance of Substring in a String

In our next example, we can see that we have two instances of the “abc” substring within the greater text string.

We will see what the result is, if we don’t specify the start_num argument.

These are our steps.

1. We will enter the following formula in cell C4.

=SEARCH(A4,B4)

2. The SEARCH Function returns a value of 1. It picks up the first instance of the substring. The first instance of the substring “abc” starts at the 1st character of the greater text string.

3. Now we will use the FIND Function to locate the “abc” substring within the greater text string. So insert the following formula in cell D4.

=FIND(A4,B4)

Discussion

Both SEARCH and FIND functions return the value 1, since, without specifying a start_num, the functions return the position of the first instance of the “abc” substring.

Also read: How to Find the Last Space in Text String in Excel?

Example 4 – Finding the Second Instance of Substring in a String

For our next example, we now want to return the position of the second “abc” substring within the greater text string.

We will do this by specifying a start_num argument in the FIND and SEARCH functions.

     These are our steps.

1. We will enter the following formula in cell C5.

=SEARCH(A5,B5,4)

2. The SEARCH Function returns a value of 9. It picks up the second instance of the “abc” substring. Since we specified that the function has to start counting from the fourth character in the greater text string.

3. Now, we will use the FIND Function to locate the second instance of the “abc” substring. So, in cell D5, enter the following formula.

=FIND(A5,B5,4)

Discussion

By specifying the start_num argument, we enabled both the SEARCH and FIND Functions to locate the second instance of the “abc” substring rather than the first one.

We basically told Excel to start searching from the 4th character of the greater text string.

We can see that the result, in this case, is the same for both of our functions.

Also read: Find the Closest Match in Excel (Nearest Value)

Example 5 – Finding Case Sensitive String

In the following example, we have the “abc” substring in lowercase, whereas our greater text string “ABCDEF” is in uppercase.

We want to see what result the SEARCH and FIND Functions will return, respectively.

These are our steps.

1. We will enter the following formula in cell C6.

=SEARCH(A6,B6)

2. The SEARCH Function returns a value of 1. Since the “abc” substring starts at the first character of the greater text string. Note that the SEARCH function does not make a distinction between lowercase and uppercase. It considers ‘abc’ and ‘ABC’ as the same string.

3. Now, we will use the FIND Function to locate the “abc” substring within the greater text string. So, in cell D6, enter the following formula.

=FIND(A6,B6)

Discussion

For this example, we can see that our SEARCH and FIND Functions are returning different results.

The SEARCH Function returns a position of 1, whereas the FIND Function returns the #VALUE! error.

This is because the FIND Function is case sensitive, whereas the SEARCH function is not. Therefore, the FIND Function doesn’t find a match in the greater text string.

This is the first major difference between the two functions. So always remember if you want to match a substring, regardless of case then use the SEARCH Function.

Example 6 – Finding substring Containing Wildcard Character

Now, we are going to look at matching a substring that contains a wildcard character.

The question mark (?) can be used as a wildcard character and matches any single character.

These are our steps.

1. We will enter the following formula in cell C7.

=SEARCH(A7,B7)

2. The SEARCH Function returns a value of 5. Since the SEARCH Function accepts wildcard characters, so the wildcard character (?) takes the place of the a. The substring, which includes the wildcard character, is thus matched to “abc” within the greater text string.

The substring “abc” begins at the fifth character of the text string “cklmabc”.

3. We will now use the FIND Function to locate the position of the substring. So, in cell D7, enter the following formula.

=FIND(A7,B7)

Discussion

The result from the FIND Function is the #VALUE! error since the FIND Function does not read wildcard characters.

Therefore, it does not get a match.

So it’s important to note that if you want to use wildcard characters such as (?) or (*), then you need to use the SEARCH Function. This is the second major difference between the two functions.

Also read: How to Replace Asterisks in Excel

Example 7 – Finding the Position of Question Mark

In this example, we want to see how we can use the SEARCH and FIND Functions to locate the position of the actual question mark in the greater text string.

These are our steps.

1. We will enter the following formula in cell C8.

=SEARCH("~?",B8)

The value of 7 is returned by the SEARCH Function, since the question mark is the seventh character in the greater text string.

2. We will now use the FIND Function to return the position of the actual question mark. So, insert the following formula in cell D8.

=FIND(A8,B8)

Discussion

Since the SEARCH Function accepts wildcard characters, when we want to find an actual question mark, we need to use the tilde character (~) before the question mark in the formula.

Since the question mark is within our greater text string, we can input the cell reference directly into our FIND formula. We don’t need to use the tilde character (~) in this case.

Also read: How to Remove Question Marks from Text in Excel?

Example 8 – Matching Substring that Contains Wildcard Character

Now, we are going to look at matching a substring that contains another wildcard character. The asterisk (*) can be used as a wildcard character and matches any number of characters.

These are our steps.

1. We will enter the following formula in cell C9.

=SEARCH(A9,B9)

We see that a value of 2 has been returned by the SEARCH Function. Since the a*d substring with any number of characters between the a and the d, in the greater text string starts at position 2.

2. Now we will use the FIND Function to locate “a*d”, within the greater text string. So, insert the following formula in cell D9.

=FIND(A9,B9)

Discussion

Our SEARCH Function accepts the (*) wildcard. So, ultimately, the SEARCH Function returns the position of the substring as 2.

The FIND Function, on the other hand, doesn’t accept wildcard characters, so again, we get a #VALUE! Error since it doesn’t get a match.

Example 9 – Locating the Position of the Actual Asterisk

In this example, we want to see how we can use the SEARCH and FIND Functions to locate the position of the actual asterisk in the greater text string.

These are our steps.

1. We will enter the following formula in cell C10.

=SEARCH("~*",B10)

We see a value of 6 returned by our SEARCH Function since this is the position of the actual asterisk in the greater text string.

2. Now, we will use the FIND Function to return the position of the asterisk in the greater text string. In cell D10, enter the following formula.

=FIND(A10,B10)

Discussion

When we want to find an actual asterisk, we need to use the tilde character (~) before the asterisk in the formula with our SEARCH Function.

With our FIND Function, since the asterisk is actually in the greater text string, we can input the cell reference directly into the formula.

Also read: How to Extract Number from Text in Excel (Beginning, End, or Middle)

Example 10 – Finding a Character Not Present in the String

In our following example, the character “q” is not in the greater text string. So, let’s see what is returned when we use our SEARCH and FIND Functions.

These are our steps.

1. We will enter the following formula in cell C11.

=SEARCH(A11,B11)

We see that there is a #VALUE! Error since the SEARCH Function has not found the “q” character in the greater text string.

2. Now we will use the FIND Function to locate “q”, within the greater text string. So, insert the following formula in cell D11.

=FIND(A11,B11)

Discussion

When the SEARCH and FIND Functions can’t find a character or string, the #VALUE! Error is returned.

Note: You will also get #VALUE! errors if you specify a start_num that is less than or equal to zero, or if the start_num is greater than the within_text parameter length.

Using the SEARCH and FIND Functions for More Complex Tasks

Now, let’s take a look at using the SEARCH Function and FIND Functions to help us out with slightly more complex tasks.

You will often encounter these functions utilized in combination with other text functions to solve challenging text manipulation tasks.

Example 1: Extracting all the characters following the dash

Below, we have a list of product codes in column A. The product code is made up of 3 letters, a dash, and numbers.

The numbers represent the price of the product. We want to extract the price only in column B.

These are our steps.

1. We will enter the following formula in cell B2.

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

2. Drag the formula down the column using the fill handle to populate the rest of the column with the respective prices.

3. Now select range B2:B9.

4. Press CTRL+SHIFT+$ on your keyboard to apply the Currency format. You should see the following.

Discussion

To understand the formula =VALUE(RIGHT(A2,LEN(A2)-SEARCH(“-“,A2))), we will break it down into its component parts:

• SEARCH(“-“,A2)

The SEARCH Function is used to locate the position of the dash in the text in cell A2.

• LEN(A2)-SEARCH(“-“,A2)

The LEN Function is used to return the number of characters in the entire text string in cell A2. The position of the dash character is then subtracted from the number of characters in the entire text string. This will give us the number of characters after the dash.

• RIGHT(A2,LEN(A2)-SEARCH(“-“,A2))

The RIGHT Function is used to return the number of characters from the end of the text string up until the dash. This allows us to isolate the price component of the text string.

• VALUE(RIGHT(A2,LEN(A2)-SEARCH(“-“,A2)))

The VALUE Function converts the text string that represents a number in cell A2, to an actual number.

Finally, the Currency format is applied to give us the price of our product.

Example 2: Find the nth occurrence of a certain character in a text string

Now, let’s see how to use the FIND Function in a more complex formula.

Below we have product codes that comprise letters, numbers, and dashes in column A. We want to find the position of the second dash for each product code in column B.

We also want to obtain the position of the fourth dash for each product code in column C.

These are our steps.

1. To find the position of the second dash for the product code in cell A2, enter the following formula in cell B2.

=FIND(CHAR(5),SUBSTITUTE(A2,"-",CHAR(5),2))

The number 9 is returned, since the second dash is the 9th character in the text string in cell A2.

2. We will drag the formula down to populate the rest of the column.

3. In order to see the position of the fourth dash enter the following formula in cell C2.

=FIND(CHAR(5),SUBSTITUTE(A2,"-",CHAR(5),4))

4. We drag the formula down the column, using the fill handle.

Discussion

The general syntax for finding the nth character (where the actual character occurs multiple times) in a text string is:

=FIND(CHAR(5), SUBSTITUTE(cell, actual_character, CHAR(5), n)) where:

• CHAR(5) is an unprintable character in the ASCII system.
• cell is the cell reference containing the entire text string.
• actual_character is the dash in this case that we want to locate in the text string. There must be multiple instances of this character.
• n is the denoted instance of the actual_character that you would like to locate.

Also read: Convert Text to Date in Excel

When Should You Use Excel’s FIND Function Instead of SEARCH Function?

You should choose to use Excel’s FIND function instead of the SEARCH function when you need to locate a specific text string within another and require that the match be case-sensitive.

For instance, if you’re working with a list of product codes that have a mix of uppercase and lowercase letters, and you need to find and match them precisely, the FIND function would be more suitable for the task.

Remember that the FIND function also does not recognize wildcard characters, so if you’re looking for an exact match without considering wildcards, then the FIND function might be your best choice.

The main difference between the SEARCH and FIND Functions is that the SEARCH Function is case-insensitive and accepts wildcards. The FIND Function, on the other hand, is case-sensitive and does not accept wildcards.

I hope you found this tutorial useful please feel free to comment below.

Other Excel articles you may also like:

• SUMPRODUCT vs SUMIFS Function in Excel
• Macro vs. VBA – What’s the Difference?
• Row vs Column in Excel
• Microsoft Excel vs. Google Sheets
• XLOOKUP Vs INDEX MATCH in Excel