Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 2 Next »

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 Tag

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:");"]")

String

Tokens

[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: 

RTRIM(LTRIM(REGEXTRACT(#String;"\\[adv:(.*?)\\]");"[adv:");"]")

String

Tokens

[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: 

RTRIM(LTRIM(REGEXTRACT(#String;"\\[notag:(.*?)\\]");"[notag:");"]")

String

Tokens

[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]

  • No labels