Pulling data out of an array

Question

I’ve got a column that has data like this:
[{"Tags":"action"},{"Tags":"sci-fi"},{"Tags":"violence"},{"Tags":"youngadult"}]
I’d like to just extract the words, “action”, “sci-fi”, “violence”, “youngadult” so I can turn this into a multi-select column.
Any idea how I can use a formula or automation to do this? Thanks in advance!

Justin_Barrett · Accepted Answer

This can be done by using a nested collection of SUBSTITUTE() functions to remove the pieces you don’t want. Here’s what I did:

SUBSTITUTE(
    SUBSTITUTE(
        SUBSTITUTE(
            SUBSTITUTE(
                SUBSTITUTE(Tags, '{"Tags":', ""),
            "]", ""),
        "[", ""),
    '"', ""),
"}", "")

Thank you, that was perfect! You’ve unblocked my Thanksgiving project.

TWu · Answer

This can be done by using a nested collection of SUBSTITUTE() functions to remove the pieces you don’t want. Here’s what I did:

SUBSTITUTE(
    SUBSTITUTE(
        SUBSTITUTE(
            SUBSTITUTE(
                SUBSTITUTE(Tags, '{"Tags":', ""),
            "]", ""),
        "[", ""),
    '"', ""),
"}", "")

Thank you, that was perfect! You’ve unblocked my Thanksgiving project.

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded