Werrej
Ilbieraħ fil-maratona 30 funzjoni Excel fi 30 jum 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.
Fl-18-il jum tal-maratona, se niddedikaw l-istudju tal-funzjoni FITTEX (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 FITTEX (SEARCH). If you have some tricks or examples of working with this function, please share them in the comments.
Function 18: SEARCH
funzjoni FITTEX (SEARCH) searches for a text string within another text string, and if found, reports its position.
How can I use the SEARCH function?
funzjoni FITTEX (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
funzjoni FITTEX (SEARCH) has the following syntax:
SEARCH(find_text,within_text,[start_num])
ПОИСК(искомый_текст;текст_для_поиска;[нач_позиция])
- find_test (search_text) huwa t-test li qed tfittex.
- ġewwa_test (text_for_search) – a text string within which the search is performed.
- start_num (start_position) – jekk mhux speċifikat, it-tfittxija tibda mill-ewwel karattru.
Traps SEARCH (SEARCH)
funzjoni FITTEX (SEARCH) will return the position of the first matching string, case insensitive. If you need a case sensitive search, you can use the function ISSIB (FIND), which we will meet later in the marathon 30 funzjoni Excel fi 30 jum.
Example 1: Finding text in a string
Uża l-funzjoni FITTEX (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 FITTEX (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 JEKK Żball (IFERROR) so that instead of executing the function FITTEX (SEARCH) display the corresponding message. Function JEKK Żball (IFERROR) was introduced in Excel starting in version 2007. In earlier versions, the same result could be obtained using IF (IF) flimkien ma 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 FITTEX (SEARCH), for an error – use the function ISNUMBER (ISNUMBER). If the string is found, the result FITTEX (SEARCH) will be a number, which means a function ISNUMBER (ISNUMBER) will return TRUE. If the text is not found, then FITTEX (SEARCH) will report an error, and ISNUMBER (ISNUMBER) will return FALSE.
In the value of the argument find_test (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 Somma (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 FITTEX (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 FITTEX (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)))