The SEARCH function

The SEARCH function is a text function which scans through a selected string for a defined set of characters. If it finds that set of characters, it will output the character position where the string starts within the larger field.

This is the format of the SEARCH function:

=SEARCH(Text To Find, Text To Search, Starting Position)

Here is an example of the SEARCH function:

=SEARCH( “kitty”, “The little girl said she lost her kitty. We need to help her!”) = 35

This function is valuable when there are known values, symbols, or identifiers, that can aid in separating important sub-strings from a larger string. These can come in the form of delimiters, or as headers / descriptions which precede or supersede the sought-after information.

An example of a functional implementation of this would be exporting a list of requests from your database/application and wanting to perform an analysis on sessions performed. In the notes section of each session employees are asked to rate the interaction as ‘Session-Exceeded’, ‘Session-Achieved’, ‘Session-Lacking’. By using these terms and the SEARCH function it is possible to quickly assign values to each one.

=IFS(ISNUMBER(SEARCH("session-exceeded",E4)),3,ISNUMBER(SEARCH("session-achieved",E4)),2,ISNUMBER(SEARCH("session-lacking",E4)),1)

When SEARCH cannot find the string it is asked to look for, it returns an error. By using the ISNUMBER function, we can turn successful searches into TRUE, and unsuccessful searches into FALSE.

* Note this example uses the IFS function, ISNUMBER function, and the SEARCH function, to quickly assign values to a set of strings with minimal manual work. For more information on each function, please visit their respective function pages. *