I am using Supermetrics to pull Facebook Ad Set Targeting data. But within the massive block of text that is generated, I need to filter it down to just the Excluded Custom Audience text. This is one example of the Ad Set Targeting text:

{"age_max":65,"age_min":18,"app_install_state":"not_installed","**excluded_custom_audiences":[{"id":"6054936712957","name":"ios devices"},{"id":"6054936847157","name":"android devices"},{"id":"6131431891357","name":"People who used your app: OfferUp"},{"id":"6247466972157","name":"Exclusions.Android"},{"id":"6247467469757","name":"Exclusions.iOS"}]**,"geo_locations":{"countries":["US"],"location_types":["home","recent"]},"locales":[24,6],"targeting_optimization":"expansion_all","user_device":["Android_Smartphone","Android_Tablet"],"user_os":["Android"],"brand_safety_content_filter_levels":["FACEBOOK_STANDARD","AN_STANDARD"]}.

And I need to extract just the bolded text. Geo_location does not consistently follow Excluded_custom_audiences, so that can't be used.

CodePudding user response：

Try the following:

=RegexExtract(A1,"""excluded_custom_audiences"":.*?]")

It translates to: extract everything (from A1) starting from "excluded_custom_audiences": up to the next occurence of ].

CodePudding user response：

try:

=ARRAYFORMULA(REGEXREPLACE(SPLIT(FLATTEN(SPLIT(REGEXEXTRACT(A1,
 """excluded_custom_audiences"":\[(.*?)\]"), ",")), ":"), "\{|\}|""", ))