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.
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:
=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:
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:
=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 “6”, since the first “e” appears precisely at position 6. You can use this type of search to perform text analyses, for example.
=SEARCH(A1,A2)
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 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”.
=REPLACE(A2,SEARCH (A3,A2),6,"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.
=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 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:
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.
There are several reasons why you may want to find duplicates in Excel – to filter out errors or analyze double entries. Excel can automatically display these duplicates to you. Thanks to color 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.
Analyses and calculations in Excel can be highly complex. Excel’s worksheets let you create huge tables, but sometimes you just want to answer a very simple question: How many cells in the table contain a number? In big worksheets, it can be virtually impossible to check the whole thing manually. The COUNT function in Excel was created specifically to solve this problem.
Excel offers over 400 useful functions. The AND function is one of the most popular ones. It allows you to check whether spreadsheet values meet certain conditions in just a few clicks. This article provides step-by-step instructions on how to use Excel AND with the help of an example. We will also show you how to get the most out of Excel by combining it with the IF function.
The MOD function in Excel helps to calculate the remainder from a division operation and is useful when you need to manage production data, for example. This handy function makes everyday office tasks much easier for a diverse range of situations. Explore professional tips and examples on how to use Excel’s MOD function.
Excel is well-known for its helpful functions and features when it comes to managing and organizing large amounts of data. One of them is the relatively new XLOOKUP function, which makes it easier to search for specific values within a dataset. Learn how to use the XLOOKUP function in Excel and why it replaces functions like the VLOOKUP or HLOOKUP here.
Provide powerful and reliable service to your clients with a web hosting package from IONOS.
