Syntax
REGEXTRACT_LIST)(<string>;<token regex string>)
Description
Using the ‘REGEXTTRACT_LIST’ Workbook function extracts tokens that match to a regular expression. This function complements the Workbook function ‘REGEXTRACT’.
‘REGEXTRACT_LIST’:
does not hide records that do not contain a string that matched the given RegEx
returns null in case a string has no RegEx match
returns a list instead of a string
if the string has a single RegEx match, the function will return a single element list
if the string has multiple RegEx matches, the function will return them as list elements
if the string has no RegEx match, the function will return an empty list
Example
Given the following example string:
[ini: SS21 ] [cou: DE][cou: DE] [fun: Hearts] [fmt: Banner] [opt: Brand Awareness][adv: Colmar] [ini: BAU] [chn: Display] [plt: DV360] [fun: Hearts] [opt: Brand Awareness] [cou: DE] [str: Affinity] [fmt: HalfPage] [dev: Desktop]
[ini: SS21 ] [cou: DE][cou: DE] [fun: Hearts] [fmt: Banner] [opt: Brand Awareness][adv: Colmar] [ini: BAU] [chn: Display] [plt: DV360] [fun: Hearts] [opt: Brand Awareness] [cou: DE] [str: Affinity] [fmt: HalfPage] [dev: Desktop]
[ini: SS21 ] [cou: DE][cou: DE] [fun: Hearts] [fmt: Banner] [opt: Brand Awareness][adv: Colmar] [ini: BAU] [chn: Display] [plt: DV360] [fun: Hearts] [opt: Brand Awareness] [cou: DE] [str: Affinity] [fmt: HalfPage] [dev: Desktop] [tgt: AffinityMix]
[ini: SS21 ] [cou: DE][cou: DE] [fun: Hearts] [fmt: Banner] [opt: Brand Awareness][adv: Colmar] [ini: BAU] [chn: Display] [plt: DV360] [fun: Hearts] [opt: Brand Awareness] [cou: DE] [str: Affinity] [fmt: HalfPage] [dev: Desktop] [tgt: AffinityMix]and the applied formula:
RTRIM(LTRIM(REGEXTRACT(#String;"\\[tag:(.*?)\\]");"[tag:");"]")Example Two Records RegEx Match
Formula:
RTRIM(LTRIM(REGEXTRACT(#String;"\\[tgt:(.*?)\\]");"[tgt:");"]") | |
|---|
[ini: SS21 ] [cou: DE][cou: DE] [fun: Hearts] [fmt: Banner] [opt: Brand Awareness][adv: Colmar] [ini: BAU] [chn: Display] [plt: DV360] [fun: Hearts] [opt: Brand Awareness] [cou: DE] [str: Affinity] [fmt: HalfPage] [dev: Desktop]
| Â |
[ini: SS21 ] [cou: DE][cou: DE] [fun: Hearts] [fmt: Banner] [opt: Brand Awareness][adv: Colmar] [ini: BAU] [chn: Display] [plt: DV360] [fun: Hearts] [opt: Brand Awareness] [cou: DE] [str: Affinity] [fmt: HalfPage] [dev: Desktop]
| Â |
[ini: SS21 ] [cou: DE][cou: DE] [fun: Hearts] [fmt: Banner] [opt: Brand Awareness][adv: Colmar] [ini: BAU] [chn: Display] [plt: DV360] [fun: Hearts] [opt: Brand Awareness] [cou: DE] [str: Affinity] [fmt: HalfPage] [dev: Desktop] [tgt: AffinityMix]
| AffinityMix
|
[ini: SS21 ] [cou: DE][cou: DE] [fun: Hearts] [fmt: Banner] [opt: Brand Awareness][adv: Colmar] [ini: BAU] [chn: Display] [plt: DV360] [fun: Hearts] [opt: Brand Awareness] [cou: DE] [str: Affinity] [fmt: HalfPage] [dev: Desktop] [tgt: AffinityMix]
| AffinityMix
|
Example Four Records RegEx Matches
Formula:
| |
|---|
[ini: SS21 ] [cou: DE][cou: DE] [fun: Hearts] [fmt: Banner] [opt: Brand Awareness][adv: Colmar] [ini: BAU] [chn: Display] [plt: DV360] [fun: Hearts] [opt: Brand Awareness] [cou: DE] [str: Affinity] [fmt: HalfPage] [dev: Desktop]
| Colmar
|
[ini: SS21 ] [cou: DE][cou: DE] [fun: Hearts] [fmt: Banner] [opt: Brand Awareness][adv: Colmar] [ini: BAU] [chn: Display] [plt: DV360] [fun: Hearts] [opt: Brand Awareness] [cou: DE] [str: Affinity] [fmt: HalfPage] [dev: Desktop]
| Colmar
|
[ini: SS21 ] [cou: DE][cou: DE] [fun: Hearts] [fmt: Banner] [opt: Brand Awareness][adv: Colmar] [ini: BAU] [chn: Display] [plt: DV360] [fun: Hearts] [opt: Brand Awareness] [cou: DE] [str: Affinity] [fmt: HalfPage] [dev: Desktop] [tgt: AffinityMix]
| Colmar
|
[ini: SS21 ] [cou: DE][cou: DE] [fun: Hearts] [fmt: Banner] [opt: Brand Awareness][adv: Colmar] [ini: BAU] [chn: Display] [plt: DV360] [fun: Hearts] [opt: Brand Awareness] [cou: DE] [str: Affinity] [fmt: HalfPage] [dev: Desktop] [tgt: AffinityMix]
| Colmar
|
Example no RegEx Match
Formula:
| |
|---|
[ini: SS21 ] [cou: DE][cou: DE] [fun: Hearts] [fmt: Banner] [opt: Brand Awareness][adv: Colmar] [ini: BAU] [chn: Display] [plt: DV360] [fun: Hearts] [opt: Brand Awareness] [cou: DE] [str: Affinity] [fmt: HalfPage] [dev: Desktop]
| Â |
[ini: SS21 ] [cou: DE][cou: DE] [fun: Hearts] [fmt: Banner] [opt: Brand Awareness][adv: Colmar] [ini: BAU] [chn: Display] [plt: DV360] [fun: Hearts] [opt: Brand Awareness] [cou: DE] [str: Affinity] [fmt: HalfPage] [dev: Desktop]
| Â |
[ini: SS21 ] [cou: DE][cou: DE] [fun: Hearts] [fmt: Banner] [opt: Brand Awareness][adv: Colmar] [ini: BAU] [chn: Display] [plt: DV360] [fun: Hearts] [opt: Brand Awareness] [cou: DE] [str: Affinity] [fmt: HalfPage] [dev: Desktop] [tgt: AffinityMix]
| Â |
[ini: SS21 ] [cou: DE][cou: DE] [fun: Hearts] [fmt: Banner] [opt: Brand Awareness][adv: Colmar] [ini: BAU] [chn: Display] [plt: DV360] [fun: Hearts] [opt: Brand Awareness] [cou: DE] [str: Affinity] [fmt: HalfPage] [dev: Desktop] [tgt: AffinityMix]
| Â |
Â