ọdịnaya
Ụnyaahụ na marathon 30 Excel na-arụ ọrụ n'ime ụbọchị 30 we recognized the types of errors using the function ERROR.Ụdị (ERROR TYPE) and made sure that it can be very useful for correcting errors in Excel.
N'ụbọchị 18th nke marathon, anyị ga-etinye akwụkwọ nyocha nke ọrụ ahụ Chọọ (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 Chọọ (SEARCH). If you have some tricks or examples of working with this function, please share them in the comments.
Function 18: SEARCH
ọrụ Chọọ (SEARCH) searches for a text string within another text string, and if found, reports its position.
How can I use the SEARCH function?
ọrụ Chọọ (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
ọrụ Chọọ (SEARCH) has the following syntax:
SEARCH(find_text,within_text,[start_num])
ПОИСК(искомый_текст;текст_для_поиска;[нач_позиция])
- Find_ederede (search_text) bụ ederede ị na-achọ.
- n'ime_ederede (text_for_search) – a text string within which the search is performed.
- mmalite_num (start_position) - ọ bụrụ na akọwapụtaghị ya, ọchụchọ ga-amalite site na agwa mbụ.
Traps SEARCH (SEARCH)
ọrụ Chọọ (SEARCH) will return the position of the first matching string, case insensitive. If you need a case sensitive search, you can use the function NWETA (FIND), which we will meet later in the marathon 30 Excel na-arụ ọrụ n'ime ụbọchị 30.
Example 1: Finding text in a string
Jiri ọrụ ahụ Chọọ (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 Chọọ (SEARCH) will return the position number of its first character in the text string. If not found, the result will be an error message # URU! (#SO).
In case the result is an error, you can use the function IFEROR (IFERROR) so that instead of executing the function Chọọ (SEARCH) display the corresponding message. Function IFEROR (IFERROR) was introduced in Excel starting in version 2007. In earlier versions, the same result could be obtained using IF (Ọ BỤRỤ) yana 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 Chọọ (SEARCH), for an error – use the function ỌZỌ (ISNUMBER). If the string is found, the result Chọọ (SEARCH) will be a number, which means a function ỌZỌ (ISNUMBER) will return TRUE. If the text is not found, then Chọọ (SEARCH) will report an error, and ỌZỌ (ISNUMBER) will return FALSE.
In the value of the argument Find_ederede (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 ỌZỌ (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 Chọọ (SEARCH), we can find the position of this character. Its position can be specified as the value of the argument mmalite_num (start_position) in the “main” function Chọọ (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)))