The SEARCH function in Excel
is very similar to FIND in that it also returns the location of a substring in
a text string. Unlike FIND, the SEARCH function is case-insensitive and it
allows using the wildcard characters.
Syntax
The syntax for the SEARCH
function in Microsoft Excel is:
SEARCH( substring, string, [start_position] )
Parameters or Arguments
substring
The substring that you want to
find.
string
The string to search within.
start_position
Optional. It is the position
in string where the search will start. The first position is 1.
Note
► If
the SEARCH function does not find a match, it will return a #VALUE! error.
► SEARCH
returns the position of the first character of find_text inside within_text.
► Unlike
FIND, SEARCH allows the use of wildcards, and is not case-sensitive.
► SEARCH
allows the wildcard characters question mark (?) and asterisk (*), in
find_text.
► The
? matches any single character and the * matches any sequence of characters.
► To
find a literal ? or *, use a tilde (~) before the character, i.e. ~* and ~?.
Search Function Examples
Column B of the following
spreadsheet shows examples of the Excel Search function, used to search for
various characters within the text string "Original Text".
Formulas:
A
|
B
|
|
1
|
Original
Text
|
=SEARCH(
"T", A1 )
|
2
|
Original
Text
|
=SEARCH(
"t", A2 )
|
3
|
Original
Text
|
=SEARCH(
"i", A3 )
|
4
|
Original
Text
|
=SEARCH(
"i", A4, 4 )
|
Results:
A
|
B
|
|
1
|
Original
Text
|
10
|
2
|
Original
Text
|
10
|
3
|
Original
Text
|
3
|
4
|
Original
Text
|
5
|
Note that, in the above examples:
► As
the Search function is not case sensitive, the upper- and lower-case
search_text values, "T" and "t", return the same result
(see cells B1 & B2).
► In
the example in cell B4, the [start_num] argument is set to 4. Therefore the
search begins at the fourth character of the within_text string and so returns
the second occurrence of the character "i".
Common Error
#VALUE!
Occurs if either:
► The
supplied search_text is not found in the supplied within_text string;
► The
supplied [start_num] is less than zero or is greater than the length of the
supplied within_text string.
Reference:
follow us on http://arivilm.blogspot.in
like us on https://www.facebook.com/Arivilm2501/
No comments:
Post a Comment