'Filter Text Data
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.
Solution 1:[1]
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 ].
Solution 2:[2]
try:
=ARRAYFORMULA(REGEXREPLACE(SPLIT(FLATTEN(SPLIT(REGEXEXTRACT(A1,
"""excluded_custom_audiences"":\[(.*?)\]"), ",")), ":"), "\{|\}|""", ))
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | ztiaa |
| Solution 2 | player0 |