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
| Argument | Type | Description |
|---|---|---|
| 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)
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.