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?
-
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?
-
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))
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.5K Get Help
- 367 Global Discussions
- 202 Industry Talk
- 432 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 447 Show & Tell
- 29 Member Spotlight
- 1 SmartStories
- 285 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!