cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bjsebeck
Frequent Visitor

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

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?

4 REPLIES 4
DamoBird365
Super User
Super User

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.

 

DamoBird365_0-1620922049493.png

 

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 😉

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.

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.

 

DamoBird365_0-1620931974763.png

 

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 😉

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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Users online (2,338)