need a formula to retrieve a value from an array
hi everyone
we receive an array from external source to our sheet with questions and answers (the id of the question never change on each line)
this is the content of the cell value that I receive from external source :
[{"Answer":"fix","AnswerOptions":["fix","mobile"],"Question":"Do you have a computer","QuestionID":"a6e945c5-01d1-4433-9fbe-4fef2225f3ff","SelectedOptions":[0]},{"Answer":"testOK","Question":"Can you precise that","QuestionID":"dd4d8045-83a7-4843-b76c-c0db7ec74559"}]
i need a formula to retrieve the answer for the questionID: dd4d8045-83a7-4843-b76c-c0db7ec74559
i've try with a combination of left, right and find but without success
thanks for your help !
Answers
-
Anyone can help us ?
Maybe @Paul Newcome ? 😊
-
What exactly would be the expected output from the string above? Are you able to provide a screenshot for context?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Yes of course :)
That's more cleary for you ?
-
Will it always be the piece immediately after the second instance of "Answer": and end at the comma?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Correct :-)
-
Give this a go...
=MID([String Column]@row, FIND("!", SUBSTITUTE([String Column]@row, "Answer":", "!", 2)) + 1, FIND(""", [String Column]@row, FIND("!", SUBSTITUTE([String Column]@row, "Answer":", "!", 2)) + 1) - (FIND("!", SUBSTITUTE([String Column]@row, "Answer":", "!", 2)) + 1))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
thanks but that's not work :(
i was think that because you use commas instead semicolon (;) but i've try also with semicolon with same error message : #UNPARSEABLE
i've a question about exclamation, what is it for ?
-
i found the solution from power automate side
thanks for your help ;-)
-
The "!" portion is essentially replacing the second instance of "Answer":" with an exclamation point to make it easier to locate.
Glad you were able to find a working solution.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!