In(text, look_in, delimiter)
Category: Logical function
This function returns Boolean TRUE if text is found within any of the values listed in look_in. Otherwise, it returns Boolean FALSE.
|text||Text||Any text value or a value than can be implicitly converted to text.|
|look_in||Text (delimited values)||A list of values, separated by delimiter that are searched for the presence of the text value.
Values in this delimited string are always considered text, even if they look like a number.
|delimiter||Text||The character or characters (it can be more than one character) used to separate the individual
values within the look_in argument.
Return value type: Boolean (TRUE/FALSE)
This function is case sensitive. If the case of text doesn't match the cases of the values in look_in, even though the text itself matches, a value of FALSE will be returned. If a case insensitive match is required, force text and the values in look_in to either lowercase or uppercase, first.
Be careful when selecting the delimiting character(s) to use. If any of the values in look_in have this character/these characters embedded within them, they will be considered separate values. Select a character or combination of characters that won't appear within the look_in values.
in( 'A', 'a:aa:AAA', ':' ) //Returns FALSE
in( 'May', 'April-May-June-July', '-' ) //Returns TRUE
To use this function with column references, concatenate the delimiter between the column names to build the look_in value:
in( [TestValue], [Value1] & ':' & [Value2] & ':' & [Value3], ':')
Caution: Be careful of look_in values having the delimiter character embedded within them.
If the function is configured as follows, using '-' as the delimiter:
in( [TestValue], [Value1] & '-' & [Value2] & '-' & [Value3], '-')
The embedded dashes ('-') in South-East and North-West will be viewed as delimiters and, under the hood, the look_in values will be…
'South', 'East', 'North', 'West', 'North'
…and no match will be made.