need a formula to retrieve a value from an array

dam · 04/23/23

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 · 04/24/23

Anyone can help us ?

Maybe @Paul Newcome ? 😊

Paul Newcome · 04/24/23

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

dam · 04/24/23

Yes of course :)

That's more cleary for you ?

Paul Newcome · 04/24/23

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

dam · 04/24/23

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

Correct :-)

Paul Newcome · 04/24/23

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 · 04/25/23

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 · 04/25/23

i found the solution from power automate side

thanks for your help ;-)

Paul Newcome · 04/25/23

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.

need a formula to retrieve a value from an array

Answers

Help Article Resources

Categories