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":"a6e945c501d144339fbe4fef2225f3ff","SelectedOptions":[0]},{"Answer":"testOK","Question":"Can you precise that","QuestionID":"dd4d804583a74843b76cc0db7ec74559"}]
i need a formula to retrieve the answer for the questionID: dd4d804583a74843b76cc0db7ec74559
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
Check out the Formula Handbook template!