Садржај
Јуче на маратону 30 Екцел функција за 30 дана we recognized the types of errors using the function ЕРРОР.ТИПЕ (ERROR TYPE) and made sure that it can be very useful for correcting errors in Excel.
18. дана маратона посветићемо проучавању функције СЕАРЦХ (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). If you have some tricks or examples of working with this function, please share them in the comments.
Function 18: SEARCH
функција СЕАРЦХ (SEARCH) searches for a text string within another text string, and if found, reports its position.
How can I use the SEARCH function?
функција СЕАРЦХ (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
функција СЕАРЦХ (SEARCH) has the following syntax:
SEARCH(find_text,within_text,[start_num])
ПОИСК(искомый_текст;текст_для_поиска;[нач_позиция])
- пронађи_текст (сеарцх_тект) је текст који тражите.
- унутар_текста (text_for_search) – a text string within which the search is performed.
- старт_нум (почетна_позиција) – ако није наведено, претрага ће почети од првог знака.
Traps SEARCH (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), which we will meet later in the marathon 30 Екцел функција за 30 дана.
Example 1: Finding text in a string
Користите функцију СЕАРЦХ (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) will return the position number of its first character in the text string. If not found, the result will be an error message #ВАЛУЕ! (#SO).
In case the result is an error, you can use the function ИФЕРРОР (IFERROR) so that instead of executing the function СЕАРЦХ (SEARCH) display the corresponding message. Function ИФЕРРОР (IFERROR) was introduced in Excel starting in version 2007. In earlier versions, the same result could be obtained using IF (ИФ) заједно са ИСЕРРОР (ЕОСХИБКА).
=IFERROR(SEARCH(B5,B2),"Not Found")
=ЕСЛИОШИБКА(ПОИСК(B5;B2);"Not Found")
Example 2: Using wildcards with SEARCH
Another way to check the result returned СЕАРЦХ (SEARCH), for an error – use the function БРОЈ (ISNUMBER). If the string is found, the result СЕАРЦХ (SEARCH) will be a number, which means a function БРОЈ (ISNUMBER) will return TRUE. If the text is not found, then СЕАРЦХ (SEARCH) will report an error, and БРОЈ (ISNUMBER) will return FALSE.
In the value of the argument пронађи_текст (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), it will return the values 1/0 instead of TRUE/FALSE (TRUE/FALSE). Next, the function СУМ (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), we can find the position of this character. Its position can be specified as the value of the argument старт_нум (start_position) in the “main” function СЕАРЦХ (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)))