How to use the Excel SEARCH function

The command for searching a document is one of the most basic keyboard shortcuts and Excel shortcuts. As an alternative to the familiar shortcut [Ctrl] + [F], Excel offers the SEARCH function, which searches an active table in Excel for a specified value or term.

How to use the Excel SEARCH function

If you work with large spreadsheets, you probably know that specific words, text strings or formulas can often be difficult to find. The same applies to typos and coding errors. Similar to free alternatives to Excel and web browsers, the standard way to search in Excel is [Ctrl] + [F]. However, this keyboard shortcut is not helpful if you only want to search for a value or term in a certain range of cells or a specific section of text.

Excel with Microsoft 365 and IONOS!

Use Excel to create spreadsheets and organize your data - included in all Microsoft 365 packages!

Office Online
OneDrive with 1TB
24/7 support

In this case, the standard search or find and replace feature has clear limitations. For example, if you want to replace “and” with “or”, “sand” would also be changed to “sor”. This makes it difficult to find errors in large documents or long lines of code.

The Excel SEARCH function is useful if you need to analyze text for statistical purposes and want to know where a number, character string, letter or word appears in the text. Excel SEARCH is especially helpful when creating and analyzing content.

What is the syntax for the Excel SEARCH function?

The SEARCH function in Excel has a very simple structure. It requires two arguments: What are you searching for? Where are you searching (text or range)? The syntax looks like this:

=SEARCH( search_text, within_text )

The SEARCH or SEARCHB functions have similar syntax to FIND or FINDB. The difference is that the Excel SEARCH function is case sensitive.

You can also specify the character or position where you want to start searching from:

=SEARCH( search_text, within_text, [start_num] )

The possible arguments are as follows:

  • search_text: Text, number or phrase you want to search for
  • within_text: Search range, cell or range of cells
  • start_num: This value is optional and specifies the character in within_text where you want to start searching from. If no argument is specified, the search starts at the beginning of the text.

The result indicates where the text should be searched. If there are multiple instances, you have to nest the Excel SEARCH function.

Example of the Excel SEARCH function

The following examples show how the Excel SEARCH function can be used:

Note

The application examples shown here can be reproduced in Microsoft 365 as well as in Excel versions 2021, 2019 and 2016.

=SEARCH("e";A2;6)
Finding the position of characters using the Excel SEARCH function
The Excel SEARCH shows the position of defined character strings.

In this example, we’re looking for the position of the first “e” in the character string in cell A2, starting from the 6th position. The result is “7”, since the first “e” appears precisely at position 7. You can use this type of search to perform text analyses, for example.

=SEARCH(A1;A2)
Searching for repeated text using the Excel SEARCH function
You can use the Excel SEARCH function to find repeated sections of text.

The position of “Text” from cell A1 in “Example text” is 9. This search is not case sensitive.

Searching for the string “text” using the Excel SEARCH function
You can also use the Excel SEARCH function to search for specific strings.

You can use the same method to search for “text” as a whole word. This allows you to find misspelled words in databases, for example.

Excel SEARCH for special characters or different spellings
You can use the wildcard character “?” to search for different spellings.

In contrast to the FIND function, the SEARCH function in Excel allows the wildcard character “?”. A wildcard is useful if you need to find different spellings. This way you can avoid the #VALUE! error if the value is not identical to the spelling in the cell that is being analyzed.

Tip

Use the HiDrive cloud storage from IONOS to store all your documents centrally! You can access your data from your desktop PC or your mobile device via an app and benefit from the highest data protection standards thanks to state-of-the-art data centers.

How to do basic nesting with REPLACE and SEARCH

Through simple nesting with the REPLACE function, you can use the Excel SEARCH function to replace words. In the following case, “Example” is replaced with “Sample”.

=REPLACE(A2;SEARCH(A3;A2);7;"Sample")
Using SEARCH in Excel to change text in certain positions
You can use the Excel SEARCH function to change text in certain positions using REPLACE and SEARCH.

If you want to unlock the full potential of this formula, you can try more complex applications such as shortening or extending serial numbers or coded content.

=MID(A2;SEARCH("-";A2;SEARCH("-";A2;SEARCH("-";A2)+1))+1; SEARCH("-";A2;SEARCH("-";A2;SEARCH("-";A2)+1)+1)-SEARCH("-";A2;SEARCH("-";A2;SEARCH("-";A2)-1))-3)
The Excel SEARCH function: Shortening product codes
The Excel SEARCH function is helpful if you want to shorten product codes or a sequence of numbers.

Summary of the Excel SEARCH function

The Excel SEARCH function may seem complicated at first glance. However, this function is very useful, especially if you need to clean up or standardize databases. The SEARCH function can be easily combined with the FIND function. Just remember the following:

  • The wildcard character “?” only works with the SEARCH command.
  • Case-sensitive searches are only possible with the FIND function.

In addition to SEARCH, you can also use the SEARCHB function in Excel. Both functions have the same structure, but they differ in the types of characters they can read. The SEARCH function in Excel is used for the Latin alphabet, whereas the SEARCHB function is used for characters in Chinese, Japanese, and Korean. This has to do with how the characters are counted.

Microsoft 365 for Business with IONOS!

Powerful Exchange email and the latest versions of your favorite Office apps on any device - get started with our free setup assistance.

50 GB Exchange email account
1TB OneDrive cloud storage
24/7 expert support
Tip

If you would like to find out more about common Excel functions, our Excel articles in the Digital Guide can help you:

We use cookies on our website to provide you with the best possible user experience. By continuing to use our website or services, you agree to their use. More Information.