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?

4 comments

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

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

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.
Channeled
APP
·
·
Hi Pedram N.! This thread was recently closed by our Support team. If you have a moment, please share your feedback: