ID EN
…
#Text

SEARCH

Excel Functions

Get the location of substring in a string

Syntax

EXCEL 
=SEARCH(find_text, within_text, [start_num])

Arguments

Parameter Description
find_text The substring to find.
within_text The text to search within.
start_num [optional] Starting position. Optional, defaults to 1.

Return Value

A number representing the location of substring

Details

The SEARCH function returns the position (as a number) of one text string inside another. In the most basic case, you can use SEARCH to locate the position of a substring in a text string. You can also use SEARCH to check if a cell contains specific text. SEARCH is not case-sensitive, which means it does not distinguish between uppercase and lowercase letters. In addition, SEARCH supports the use of wildcards like *?~, allowing more flexible search patterns. Here are a few key points to remember about the SEARCH function: Note: The SEARCH function is similar to the FIND function. Both functions return the position of one text string inside another. However, unlike FIND, SEARCH is not case-sensitive and does support wildcards. The basic syntax of the SEARCH function looks like this The SEAR

Examples

Basic syntax

The basic syntax of the SEARCH function looks like this

EXCEL 
SEARCH(find_text,within_text,[start_num])
Basic example

The SEARCH function is designed to look inside a text string for a specific substring. When SEARCH locates the substring, it returns the position of t

EXCEL 
=SEARCH("p","apple") // returns 2
=SEARCH("z","apple") // returns #VALUE!
=SEARCH("apple","Pineapple") // returns 5
Basic example

Note that text values entered directly into SEARCH must be enclosed in double quotes (""). Unlike the FIND function, the SEARCH function is not case-s

EXCEL 
=SEARCH("a","Apple") // returns 1
=SEARCH("A","Apple") // returns 1
=SEARCH("Apple","Pineapple") // returns 5
Forcing a TRUE or FALSE result

By default, the SEARCH function returns a number when a search string is found and a #VALUE! error when not. This is inconvenient in cases where you s

EXCEL 
=ISNUMBER(SEARCH("p","apple")) // returns TRUE
=ISNUMBER(SEARCH("z","apple")) // returns FALSE
If cell contains

Once you have a TRUE or FALSE result, you can combine the SEARCH function with the IF function to create "if cell contains" logic. The generic pattern

EXCEL 
=IF(ISNUMBER(SEARCH(substring,A1)), "Yes", "No")
If cell contains

The formula in C5, copied down, is:

EXCEL 
=IF(ISNUMBER(SEARCH("abc",B5)),"x","")

See Also

FIND REPLACE SUBSTITUTE