SEARCH and FIND functions

Try using mapflo for data analysis. It’s great for joining datasets. Get Started for Free.

Summary

SEARCH and FIND functions identify if a cell contains a particular character or string.

It can be helpful for segmenting say products, URLs etc. by identifying if say a URL contains a particular string.

In the example below we use SEARCH function to see if a URL contains “CPC”.

SEARCH is not case sensitive while FIND is case sensitive.

Syntax

=SEARCH(find_text,within_text,[start_num]) where start_num is where in the find_text argument you want to start searching.

The formula returns the character number in the find_text where the within_text is matched.

In the example above, =SEARCH(“CPC”,A2,1) returns 42 as cpc is the 42nd character in the find_text of ‘example.com?utm_source=google&utm_medium=cpc’

where find_text can be:

• a string, e.g. =SEARCH(“CPC”,A2,1)
• a number, e.g. =SEARCH(“1”,A2,1) [note, this is a string match so any number that contains a 1 will match]
• the wild card character ?, e.g. =SEARCH(“?PC”,A2,1)

Using SEARCH and FIND for data segmentation

If you are using SEARCH or FIND to segment yur dataset, then you normally want to know if the string contains the value, not where in the string the value starts.

We can use the ISNUMBER function which returns TRUE if the string is matched or FALSE if there is no match.

=ISNUMBER(SEARCH(“CPC”,A2,1)) returns TRUE or FALSE

mapflo makes data segmentation easy

mapflo is a much easier way of segmenting data. You can populate a column based conditions. Conditions can use Regex for more complex matching. Conditions run in order and you can grab and move conditions to change process order.

Learn more about mapflo’s analysis superpowers.

Get started for free