Home
Events
Groups
Members

00 Clay Announcements
01 Community General
01 Props and Wins
01 Watercooler
01 Welcome and Intros
02 Bugs
02 Starting with Clay
02 Support
03 Feedback & Requests
03 Tutorials & Inspiration
04 FT Job Opps
Gtm Career Advice
Member Spotlight

Community Guidelines

Powered by Tightknit

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?

Extracting JSON or Text from Cells Using Excel Formula | Clay

4 comments

· Sorted by Oldest

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

·

·

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: