Is it possible to use Power Automate to automatically split text to columns in Excel?

[NewbiePower]

Hello, 

I'm wondering if it's possible to set up a Flow in Power Automate so that when responses from MS Forms are submitted and added to an Excel document, Power Automate will automatically split text to columns for me. To better explain what I'm trying to do, this is an example of what I'm trying to create:

Sample MS Forms Questions:

When a course is selected and the MS Form is submitted, the response is exporting to Excel like this:

The entire selected option is going into one cell.

Because I need all 4 parts of the information listed in each option (Course code, CRN, Description, and Cost) for subsequent steps for registration, this 👇 is how I would like the information to be exported to Excel to streamline the workload:

Is it even possible to set up a Flow in Power Automate so the responses will split text to columns automatically without me having to do it manually as responses are submitted? Any resource I find that explains how to split text to columns, the data that is being split has already been entered into the Excel document.

If it is not possible for Power Automate to automatically split text to columns, is there a way to apply a formula or function to the "Select a course" columns so Excel will do it automatically as new responses are submitted?

Any insight or ideas would be appreciated.

Thanks

[creativeopinion · Accepted solution]

@NewbiePower You need to use the split() function. For my demo I'm storing a test value in a Compose action. You don't need this as you'll use the dynamic content from your MS Form.

Add a Compose action to store the expression. 

Click on the dynamic content tab and insert the dynamic content from the Get Response Details action (in your flow). In my case, I'm inserting the output from the Compose action with the test value.

The split() function takes two parameters:

split([string of text],[separator])

Add a comma an insert your separator between single quotes. In this case, you want to insert a comma and a space.

Run a test. The split() function will output the string of text as an array.

You can access each item in an array using the index key. The index key for an array starts at [0]. This means to access the first item in the array (in this case it's COMM 1000) you need to use [0]. The second item is [1], third is [2]... etc.

For demo sake—I'm using a Compose action to store an expression. You can do the same (then insert the output of the compose action into your Add a Row action, or you can insert the expression directly. 

Insert a question mark and the index key. In my case, I'm going to get the first item so I'm using [0].

Click on the Dynamic Content tab and place your cursor at the start of the expression by pressing the up arrow key and insert the output from the Select action.

Press OK and run a test. The Compose action has output the first item in the array. Repeat this for each item in the array you want to access.

You might find these MS Form Tutorials helpful. To speed up your flow building process. I'd recommend using a manual trigger and the Get Response Details action. This way you don't need to leave Power Automate to trigger your flow.

How to Get a Microsoft Form RSVP Response into a SharePoint List

IN THIS VIDEO:

✅ How to get a Microsoft Form Response into SharePoint

✅ How to get a Microsoft Form ID

✅ How to get a Microsoft Form response

✅ How to Build a Microsoft Form with Conditional Fields

✅ How to used Branching in Microsoft Forms

✅ How to format Microsoft Form multiple choice responses for a SharePoint multi-choice column

✅ How to parse a Microsoft Form multiple choice response as string of text

✅ How to convert a text response to a number

✅ How to use the Switch action

✅ How to create a custom email confirmation for a Microsoft Form submission

✅ How to Create a Custom View in a SharePoint list

✅ How to use the Compose Action

✅ How to write Power Automate Expressions

---

How to Get Microsoft Form File Uploads Attached to an Email 📧

In this Microsoft Power Automate Tutorial I’m going to cover where file uploads from your Microsoft Forms are saved. I’ll also show you how attach the uploaded files to an email and how to dynamically name the files and customize the recipient of the email based on the selections made in your Microsoft Form. I will also cover how to handle responses that don’t include any file uploads.

IN THIS VIDEO:

‌✅‌ Two types of MS Forms

‌✅‌ Where Microsoft Personal (OneDrive) Form File Uploads are Saved

‌✅‌ Where Microsoft Group Form File Uploads are Saved

‌✅‌ How to Add a File Upload Question to an MS Form

‌✅‌ How to get a Microsoft Form ID

‌✅‌ How to get a Microsoft Form Response

‌✅‌ How to Get the Dynamic Content Microsoft Form File Upload Content

‌✅‌ How to handle Single and Multiple Microsoft Form File Uploads

‌✅‌ How to use a Scope action to Organize and Group Your Flow Actions

‌✅‌ How to Get the File Content from an MS Form File Upload

‌✅‌ How to Collect All Files Uploaded to a MS Form and Attach to an Email

‌✅‌ How to Handle MS Form Response When a File Isn’t Uploaded

‌✅‌ How to Create an Email Key

‌✅‌ How to Send an Email to a Specific Recipient Based on Form Selection

‌✅‌ How to Create a Dynamic Output Based on Form Selection

-----

How to Add Microsoft Form File Uploads to a SharePoint List Item

In this Microsoft Power Automate Tutorial I’m going to cover how to add file uploads to a SharePoint list item. First, I’ll show you how to get the file name and content from the File Uploads from your Microsoft Form, then I’ll show you how to add those files to a SharePoint item.

IN THIS VIDEO:

✅ Two types of MS Forms

✅ Where Microsoft Personal (OneDrive) Form File Uploads are Saved

✅ Where Microsoft Group Form File Uploads are Saved

✅ How to Add a File Upload Question to an MS Form

✅ How to get a Microsoft Form ID

✅ How to get a Microsoft Form Response

✅ How to Get the Dynamic Content Microsoft Form File Upload Content

✅ How to handle Single and Multiple Microsoft Form File Uploads

✅ How to use a Scope action to Organize and Group Your Flow Actions

✅ How to Get the File Content from an MS Form File Upload

✅ How to Collect All Files from Multiple File Upload Questions

✅ How to Handle MS Form Response When a File Isn’t Uploaded

✅ How to Attach an MS Form Single File Upload to a SharePoint List Item

✅ How to Attach Multiple MS Form File Uploads to a SharePoint List Item

✅ How to Attach MS Form File Uploads to an Email

-----

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response!

👉 Level up your Power Automate skills by checking out my tutorials on YouTube
👉 Tips and Tricks on TikTok and Instagram

[NewbiePower]

Thank you very much for the detailed response. After playing with Power Automate a bit and following your instructions I finally got it to work. Thank you!