Splitting results from a multiple choice Forms response into separate lines within a cell in Excel

[bjsebeck]

I have a form with a multiple choice response and a flow that pulls the response data and dumps it over in an Excel table.

When I just do that without doing anything to the output, I get (for example) ["apple","banana"].

I've learned how to get that to turn into apple,banana by taking the output from that question and dumping it through 3 nested replace functions (one each for the [,], and " characters).

What I would like to do is to have the result in excel look like this:

apple

banana

Is there a way to have PowerAutomate paste that data into the Excel table with a line break there? 

I've already tried doing yet another replace to change the , into a <br>, but that just results in apple<br>banana.

I also need to apply this to multiple (but not all) questions on the form.  Is there a more elegant way to implement it other than just a whole series of compose actions, one for each question that I need to do this for?

[DamoBird365]

HI @bjsebeck 

This should work:

Create a compose action with a physical return line in it, and then join the result with the return line.  No need for a replace.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

[bjsebeck]

Huh.  This is interesting.

I stumbled upon a slightly more complex / different solution when continuing to poke at this.  I initialized a variable with a value of a return line, then did a replace and swapped the comma for the new variable that I just initialized.  It worked a treat. Interestingly, when I looked at the raw output, the variable initialized as \n, but when I changed the value that it initialized the variable as to \n, it didn't create the new line, just stuffed the characters into string.

When I just tried out the method that you suggested to see if there's multiple ways to solve my issue, I got an interesting response.  Repeating exactly what you have, it worked just fine.  Fed the results of your Compose 2 over to a row in an table in Excel and it does exactly what I wanted it to.  But, when I replaced the composed array with the output from a quick test form that I had been working with (my form and flow are live, so I created a little test environment where I can muck about without risking somebody filling out the form and triggering the flow while it's down) and the flow errored out, stating that the join expects an array, but the output from the form is a string.  When I first dumped the form response through split, the result was:

[

  "[\"apple\"",

  "\"bannana\"]"

]

Which I'm pretty sure implies that while they may be formatted as an array, the forms output is a string that just happens to look like an array.

So, it appears that the easiest way to do what I'm looking for is to first initialize a variable of a physical return line, then when I do the Add a Row to a Table action and am assigning what goes into which column, for those locations where I have the multiple choice answers, use the expression composer tool to do this:

replace(replace(replace(dynamic output from form,'","',ReturnLine),'["',''),'"]')

Which will take the forms string output of ["apple","bananas"] and, once fed over to Excel, result in apple and banana in the same cell with a physical return putting them on separate lines.

I have learned a lot today.

[DamoBird365]

Sorry about that one @bjsebeck 

Good news though, if you use the expression json() on your answer, you can then implement the solution I have provided.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

[Trish123]

Hi, I have the same issue. Could you please share a screenshot please. I am lost. Appreciate it .