Contents
Yesterday in the marathon 30 Excel functions in 30 days we recognized the types of errors using the function ERROR.TYPE (ERROR TYPE) and made sure that it can be very useful for correcting errors in Excel.
On the 18th day of the marathon, we will devote the study of the function SEARCH (SEARCH). It looks for a character (or characters) within a text string and reports where it was found. We’ll also look at how to deal with situations where this function throws an error.
So, let’s take a closer look at the theory and practical examples of the function SEARCH (SEARCH). If you have some tricks or examples of working with this function, please share them in the comments.
Function 18: SEARCH
Function SEARCH (SEARCH) searches for a text string within another text string, and if found, reports its position.
How can I use the SEARCH function?
Function SEARCH (SEARCH) searches for a text string within another text string. She can:
- Find a string of text inside another text string (case insensitive).
- Use wildcard characters in your search.
- Determine the starting position in the viewed text.
SEARCH Syntax
Function SEARCH (SEARCH) has the following syntax:
SEARCH(find_text,within_text,[start_num])
ПОИСК(искомый_текст;текст_для_поиска;[нач_позиция])
- find_text (search_text) is the text you are looking for.
- within_text (text_for_search) – a text string within which the search is performed.
- start_num (start_position) – if not specified, the search will start from the first character.
Traps SEARCH (SEARCH)
Function SEARCH (SEARCH) will return the position of the first matching string, case insensitive. If you need a case sensitive search, you can use the function FIND (FIND), which we will meet later in the marathon 30 Excel functions in 30 days.
Example 1: Finding text in a string
Use the function SEARCH (SEARCH) to find some text within a text string. In this example, we will be looking for a single character (typed in cell B5) within the text string found in cell B2.
=SEARCH(B5,B2)
=ПОИСК(B5;B2)
If the text is found, the function SEARCH (SEARCH) will return the position number of its first character in the text string. If not found, the result will be an error message #VALUE! (#SO).
In case the result is an error, you can use the function IFERROR (IFERROR) so that instead of executing the function SEARCH (SEARCH) display the corresponding message. Function IFERROR (IFERROR) was introduced in Excel starting in version 2007. In earlier versions, the same result could be obtained using IF (IF) together with ISERROR (EOSHIBKA).
=IFERROR(SEARCH(B5,B2),"Not Found")
=ЕСЛИОШИБКА(ПОИСК(B5;B2);"Not Found")
Example 2: Using wildcards with SEARCH
Another way to check the result returned SEARCH (SEARCH), for an error – use the function ISNUMBER (ISNUMBER). If the string is found, the result SEARCH (SEARCH) will be a number, which means a function ISNUMBER (ISNUMBER) will return TRUE. If the text is not found, then SEARCH (SEARCH) will report an error, and ISNUMBER (ISNUMBER) will return FALSE.
In the value of the argument find_text (search_text) you can use wildcard characters. Symbol * (asterisk) replaces any number of characters or none, and ? (question mark) replaces any single character.
In our example, the wildcard character is used *, so the phrases CENTRAL, CENTER, and CENTER will be found in street names.
=ISNUMBER(SEARCH($E$2,B3))
=ЕЧИСЛО(ПОИСК($E$2;B3))
Example 3: Determining the starting position for SEARCH (SEARCH)
If we write two minus signs (double negation) in front of the function ISNUMBER (ISNUMBER), it will return the values 1/0 instead of TRUE/FALSE (TRUE/FALSE). Next, the function SUM (SUM) in cell E2 will count the total number of records where the search text was found.
In the following example, column B shows:
City name | Profession
Our task is to find professions containing the text string entered in cell E1. The formula in cell C2 would be:
=--ISNUMBER(SEARCH($E$1,B2))
=--ЕЧИСЛО(ПОИСК($E$1;B2))
This formula found rows that contain the word “bank”, but in one of them this word is found not in the name of the profession, but in the name of the city. This does not suit us!
Each city name is followed by a symbol | (vertical bar), so we, using the function SEARCH (SEARCH), we can find the position of this character. Its position can be specified as the value of the argument start_num (start_position) in the “main” function SEARCH (SEARCH). As a result, city names will be ignored by the search.
Now the tested and corrected formula will count only those lines that contain the word “bank” in the name of the profession:
=--ISNUMBER(SEARCH($E$1,B2,SEARCH("|",B2)))
=--ЕЧИСЛО(ПОИСК($E$1;B2;ПОИСК("|";B2)))