Clay Icon

Extracting JSON or Text from Cells Using Excel Formula

Β·
Β·

If I have a column where some cells are just a text, and others are JSON with {'response': 'foo'} is there a formula that can return either foo or the cell contents depending on how the cell is structured?

  • Avatar of Daniel K.
    Daniel K.
    Β·
    Β·

    Hi Pedram! Happy to help! It sounds like a job of my favorite Clay integration: the AI formulas :) These formulas are capable of formatting, cleaning, and reshaping your data as required. Specifically, you can create a custom AI formula that checks if a cell contains JSON and extracts the 'response' value, or simply returns the cell's content if it's plain text; something along the lines of: If /textcolumn contains 'response' return value, else return string text from /textcolumn If there's anything else I can assist you with, please let me know! 😊 Have a great Monday, Daniel

  • Avatar of Pedram N.
    Pedram N.
    Β·
    Β·

    Ah thats a good strategy. The prompt returned a convouluted response

    JSON.stringify({{Blog Podcast Conf Appearance}}?.response?.includes('response') ? JSON.parse({{Blog Podcast Conf Appearance}}?.response) : {{Blog Podcast Conf Appearance}}?.response)

    This is the right answer for posterity:

    {{Blog Podcast Conf Appearance}}?.response?.includes('{"response":') ? JSON.parse({{Blog Podcast Conf Appearance}}.response).response : {{Blog Podcast Conf Appearance}}?.response
  • Avatar of Daniela D.
    Daniela D.
    Β·
    Β·

    Hey Pedram! Thanks for the reply. Glad that helped🌟. The response may be related to how the model interpreted the prompt instructions. Tweaking this usually helps with getting the desired response. If you can share your table url (link in your browser tab), we'd love to look into this.

  • Avatar of Channeled
    Channeled
    APP
    Β·
    Β·

    Hi Pedram N.! This thread was recently closed by our Support team. If you have a moment, please share your feedback: