Splitting text responses from Microsoft Forms into multiple excel rows.

(0) Share

Report

Posted on by CU09091452-0

I have a Microsoft Form with 12 questions, all written text reponses. I've been asked if it's possible for this form to create multiple excel rows from comma separated values.

For example this type of written answer:

Requestor: Bob

Material Code: 100, 200, 300

Material Due: 1/2/25, 1/3/25, 1/4/25

Would result in an excel sheet where each new row has Bob as the requestor, but has a separate row for 100, 200, 300, and the corresponding dates to each code.

I've been using compose and split to try this, but it isn't working out. Anyone know if it's possible to make this with Power Automate?

Categories:

Business Process Flows

I have the same question (0)

All responses (4)

Answers (0)

CU09091452-0 2 on at

Like (0)

Report

Splitting text responses from Microsoft Forms into multiple excel rows.

@Michael E. Gernaey @Chriddle

Unfortunately none of these responses have worked for this task. Thanks for the attempts.

The issue with the responses seems to be Forms isn't working within these formulas. I can't get power automate to recognize an array.

Was this reply helpful? Yes No
Michael E. Gernaey 48,644 Super User 2025 Season 2 on at

Like (0)

Report

Splitting text responses from Microsoft Forms into multiple excel rows.

Hi @CU09091452-0

Any update on this? You have been given 2 answers to resolve your issue.

Do you still need help or can you Mark the answers if they answer your question

Was this reply helpful? Yes No
Chriddle 8,081 Super User 2025 Season 2 on at

Like (0)

Report
Splitting text responses from Microsoft Forms into multiple excel rows.

You can divide this into two steps:

Create an object from the text (Compose_2)

Create an array from the object (Select)

Compose 2

This only works if the text follows exactly the same format.

If not, this part becomes more difficult ;)

addProperty( addProperty( addProperty( json('{}'), 'Requestor', split(split(outputs('Compose'), decodeUriComponent('%0A'))[0], ': ')[1] ), 'MaterialCode', split(split(split(outputs('Compose'), decodeUriComponent('%0A'))[1], ': ')[1], ', ') ), 'MaterialDue', split(split(split(outputs('Compose'), decodeUriComponent('%0A'))[2], ': ')[1], ', ') )

Select

From:

range(0, length(outputs('Compose_2').MaterialCode))

Map Requestor:

outputs('Compose_2').Requestor

Map MaterialCode:

outputs('Compose_2').MaterialCode[item()]

Map MaterialDue:

parseDateTime( outputs('Compose_2').MaterialDue[item()], 'es-es', 'd/M/yy' )

Result

The output of Compose 2 should look like this:

{ "Requestor": "Bob", "MaterialCode": [ "100", "200", "300" ], "MaterialDue": [ "1/2/25", "1/3/25", "1/4/25" ] }

From there the Select creates the array:

{ "body": [ { "Requestor": "Bob", "MaterialCode": "100", "MaterialDue": "2025-02-01T00:00:00.0000000" }, { "Requestor": "Bob", "MaterialCode": "200", "MaterialDue": "2025-03-01T00:00:00.0000000" }, { "Requestor": "Bob", "MaterialCode": "300", "MaterialDue": "2025-04-01T00:00:00.0000000" } ] }

Simply put this body into an Apply to each and create the Excel rows in it.

Was this reply helpful? Yes No
Suggested answer

Michael E. Gernaey 48,644 Super User 2025 Season 2 on at

Like (0)

Report
Splitting text responses from Microsoft Forms into multiple excel rows.

HI @CU09091452-0

Here is what I would do

Initialize a variable (array)

initialize it using Split(the answer) for Material code

Initialize a variable (array)

initialize it using Split(the answer) for Material Due

Now use an Apply to Each, with the Material Code Array as the Input

Now let me explain the next part, its not hard but

You have two arrays

they are going to have the same IndexOf values since they are the same size

So when you are looping through the Material Code Array, you will use, in a Compose

indexOf(variables('MyMaterialCodeArray'), item())

This will return the Index # of the current value of Material Code. Why do we want that? Because we need to get the Matching IndexOf (ordinal position value of the Material Due date) from the Material Due array

Once you have the value of the current Material Code Index in the Array, you can use THAT # to get the matching Index Due date by adding another compose and typing

variables('myDueDateArray')[int(outputs('Compose_Name_where_you_got_IndexOf'))]

So image like this

You have an array with

["100", "200","300"]

and

["Date1","Date2","Date3"]

you split the top array in your Apply to Each

So iteration 1, is 100, then 2 is 200 and 3 is 300

while you iterate, inside the apply to each you say

indexOf(variables('MyMaterialCodeArray'), item())

which is indexOf(variables('MyMaterialCodeArray'), "100") in the first iteration, which returns a 0, for its Ordinal Position aka index in the array

in your next compose

variables('myDueDateArray')[int(outputs('Compose_Name_where_you_got_IndexOf'))]

turns into

variables('myDueDateArray')[0]

which returns Date1 in the first iteration

And so on

Make sense?

If these suggestions help resolve your issue, Please consider Marking the answer as such and also maybe a like.

Thank you!
Sincerely, Michael Gernaey

Was this reply helpful? Yes No