Table of Contents

In(text, look_in, delimiter)

Category: Logical function


Description

This function returns Boolean TRUE if text is found within any of the values listed in look_in. Otherwise, it returns Boolean FALSE.


Arguments

ArgumentTypeDescription
textTextAny text value or a value than can be implicitly converted to text.
look_inText (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.
delimiterTextThe 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)


Remarks

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.


Examples

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.

Sample dataset:

TestValue Value1 Value2 Value3
North-West South-East North-West North

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.


See also