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.

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 analyse 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 analysing 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:

=SEARCH("e";A2;6)

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)

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

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.

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 analysed.

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")

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)

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 standardise databases. The SEARCH function can be easily combined with the FIND function. Just remember:

• 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.