need a formula to retrieve a value from an array

dam

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 !

dam

Anyone can help us ?

Maybe @Paul Newcome ? 😊

Paul Newcome

What exactly would be the expected output from the string above? Are you able to provide a screenshot for context?

dam

Yes of course :)

That's more cleary for you ?

Paul Newcome

Will it always be the piece immediately after the second instance of "Answer": and end at the comma?

dam

https://community.smartsheet.com/discussion/comment/373007#Comment_373007

Correct :-)

Paul Newcome

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))

dam

https://community.smartsheet.com/discussion/comment/373103#Comment_373103

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 ?

dam

i found the solution from power automate side

thanks for your help ;-)

Paul Newcome

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.