Excel SEARCH Function – Formula, Examples, How to Use?

The SEARCH formula in Excel is a way to find a specific word or letter in a sentence or a list of words. It’s a bit like a treasure hunt!

You can think of it like this: Imagine you have a big bag of letters, and you want to find a specific word or letter, but you don’t know where it is. The SEARCH formula is like a magic tool that can help you find it! You just have to tell it what word or letter you’re looking for, and it will tell you where it is in the bag of letters.

For example, let’s say you have a list of city names, and you want to know where the word “New” is located. You would use the SEARCH formula to tell you where it is. It might say “1”, which means the word “New” is at the very beginning of the sentence. Or it might say “5”, which means the word “New” is the 5th no. in the sentence.

It’s important to note that the SEARCH formula is case-insensitive, which means it will find the word or letter regardless of whether it’s in uppercase or lowercase.

Excel’s SEARCH formula is a useful tool for finding the position of a specific character or text within a string of text. This formula can be especially helpful when working with large amounts of data, as it allows you to quickly find specific information without having to manually search through the entire document.

Here is an example of how to use the SEARCH formula with a table of sample data:

	A	B	C
1	City	Formula	Output
2	New York	=SEARCH(“Yo”,A2)	5
3	Mumbai	=SEARCH(“m”,A3)	1
4	New Delhi	=SEARCH(“Delhi”,A4)	5
5	Gurgaon	=SEARCH(“Gurg”,A5)	1

Column A of this table contains the data, which in this case is a list of cities. Column B contains the formula, and Column C shows the final output or answer.

The syntax for the SEARCH formula is as follows:

=SEARCH(find_text, within_text, [start_num])

find_text: This is the text you are searching for within the “within_text” string.

within_text: This is the string of text that you are searching within.

start_num (optional): This is the starting position for the search. If you do not specify a starting position, the search will begin at the first character of the “within_text” string.

In the first example, we are searching for the text “Yo” within the string “New York” (A2), starting at position 1 (the default value). The formula returns 5, which is the position of the “Yo” in “New York”.

In the second example, we are searching for the text “m” within the string “Mumbai” (A3) and the formula returns 1, which is the position of the “m” in “Mumbai”

In the third example, we are searching for the text “Delhi” within the string “New Delhi” (A4) and the formula returns 5, which is the position of the “Delhi” in “New Delhi”

In the fourth example, we are searching for the text “Gurg” within the string “Gurgaon” (A5) and the formula returns 1, which is the position of the “Gurg” in “Gurgaon”

Keep in mind that the SEARCH formula is case-insensitive, which means that it will return the same result whether the searched-for text is uppercase or lowercase. Also, if the find_text is not found in the within_text, it will return #VALUE! error.

In conclusion, the SEARCH formula is a powerful tool for quickly and easily finding specific text within a string of text in Excel. With this formula, you can quickly and easily search through large amounts of data to find the information you need.

3 use cases of SEARCH formula in Excel.

The SEARCH formula in Excel can be used in a variety of ways to find specific text within a string of text. Here are three common use cases:

1. Finding specific text within a cell: One of the most basic uses of the SEARCH formula is to find a specific piece of text within a cell. For example, you might use the formula =SEARCH(“New”, A1) to find the location of the text “New” within the cell A1. This could be useful if you have a list of city names and you want to find which city names contain the text “New”.
2. Extracting specific information from a string: The SEARCH formula can be used in combination with other formulas to extract specific information from a string of text. For example, you might use the formula =SEARCH(“-“,A1) to find the location of a dash in a string and then use the LEFT or RIGHT formula to extract the text before or after the dash.
3. Finding and Replacing text: The SEARCH formula can be used to find specific text within a range of cells, and then use the result to replace it with another text. For example, you might use the formula =SEARCH(“Old”,A1:A10) to find all cells containing the text “Old” in a range of cells A1:A10, then use the Replace function to replace it with a new text, this can be useful in scenarios where you have a lot of data to process and you want to change specific text.
   
   

These are just a few examples of how you can use the SEARCH formula in Excel to find specific text within a string of text. The formula can be used in various data manipulation scenarios and it’s power comes from the ability to customize it to the specific use case. Remember that you can use it in combination with other formulas to achieve the desired result.

Top mistakes users make while using SEARCH formula in Excel.

Using the SEARCH formula in Excel can be a powerful tool for finding specific text within a string of text, but there are a few common mistakes users make that can lead to errors or unexpected results. Here are the top 4 mistakes to watch out for:

1. Not specifying the correct text to search: The SEARCH formula requires you to specify the text you want to find within the text string. If you don’t specify the correct text, you may end up finding a different text or not finding anything at all.
2. Not accounting for case-sensitivity: The SEARCH formula is case-insensitive, which means it will find the text regardless of whether it’s in uppercase or lowercase. If you’re searching for a specific case, you may want to use the FIND formula instead.
3. Not accounting for trailing spaces: Sometimes in data text may have trailing spaces that can cause issues with your formula. You need to use the TRIM function to remove any leading or trailing spaces in the text.
4. Not accounting for the format of the data: If the format of the data is not consistent, the SEARCH formula might not work as expected. For example, if you’re trying to find the location of a dash in a string, but some of the strings don’t have a dash, you’ll end up with errors.
   
   

Avoiding these mistakes can help ensure that your SEARCH formula works as expected, and you get the desired results when finding specific text within a string of text. Remember to always double check

Mention the alternative(s) to SEARCH formula in Excel.

The SEARCH formula in Excel is a powerful tool for finding specific text within a string of text, but there are a few alternative formulas and functions you can use to achieve similar results. Here are a few examples:

1. FIND formula: The FIND formula is similar to the SEARCH formula, but it is case-sensitive. It returns the position of the first character of the found text. This can be useful if you want to find a specific case-sensitive text within a string.
2. SUBSTITUTE: The SUBSTITUTE function can be used to replace a specific text within a cell. It can be used in combination with other formulas to replace a specific text within a range of cells.
   
   

These are just a few examples of alternative formulas and functions you can use in Excel to find specific text within a string of text. The specific formula or function you use will depend on the situation and the desired outcome.