Excel SEARCH: tips for using the Excel SEARCH function

Whether you’re a power user or just getting started with Excel and spreadsheets, the command for searching a document is one of the most basic keyboard shortcuts and Excel shortcuts.

It doesn't matter whether you choose the traditional Microsoft Office suite or one of the free alternatives, you can easily search text in any software, from Google Sheets to web browsers by pressing Ctrl + F.

As an alternative to this keyboard shortcut, Excel offers the SEARCH function. This function can also be used for other tasks beyond the basic function of searching an Excel spreadsheet for a search term. In this article, we’ll explain how to use this function correctly.

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. Of course, you can always use the standard Ctrl + F shortcut to search your spreadsheet. However, this keyboard shortcut is not useful if you only want to search a certain range of cells or a specific section of text.

In this case, the standard search or find and replace function 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 letter or word, character string, or number appears in the text. Excel SEARCH is especially helpful when you’re creating and analyzing content.

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:

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 from which to you want start searching:

The possible arguments are as follows:

• Find_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 from which you want to start searching. If no argument is specified, the search starts at the beginning of the text.

The result indicates where find_text begins. If there are multiple instances, you have to nest the Excel SEARCH function.

The following examples show how the Excel SEARCH function is used in practice:

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 “6”, since the first “e” appears precisely at position 6. You can use this type of search to perform text analyses, for example.

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 analyzed cell.

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

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.

The Excel SEARCH function may seem complicated at first glance. However, this function is very useful, especially if you use it 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 Asian characters in Chinese, Japanese, and Korean. This has to do with how the characters are counted.