There are several reasons why you may want to find duplicates in Excel – to filter out errors or analyse double entries. Excel can automatically display these duplicates to you. Thanks to colour highlighting, you can instantly see which entries appear more than once in your tables. And the feature is easy to use: We’ll explain how it works.
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, fromGoogle 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.
How to use the Excel SEARCH 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 analyse 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 analysing content.
The Excel SEARCH function in practice
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,Location of text to search)
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:
=SEARCH(Search text,Location of text to search,[First character])
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 Excel SEARCH function: examples
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.
The position of ‘Text’ from cell A1 in ‘Sample Text’ is 8. 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 analysed cell.
REPLACE and SEARCH: basic nesting
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: summary
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 standardise 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.