Split function: Text to columns in Excel

(0) Share

Report

Posted on by Nanoo51969

I have a Survey Form. I use power automate to update an Excel file every time a survey is submitted.

One of the survey questions is a ranking of items. The results of the ranking appears in a string separated by a comma.

example:

Compensation/Career Opportunities, Champion Team Results/Recognition, Communication, Cross Team Collaboration, Meetings, Technology/Upskill, Value Employees and Customers, Vision & Strategy, Work Life Balance (improve for teams), None from this group

I need to split these ranked items out across 10 separate columns to look like:

Compensation/Career Opportunities

Champion Team Results/Recognition

Communication

Cross Team Collaboration

Meetings

Technology/Upskill

Value Employees and Customers

Vision & Strategy

Work Life Balance (improve for teams)

None from this group

I have not been able to figure out how to do this and have not found any other forum post that addresses this.

my flow looks like the following with the function(s):

fx split(Outputs('Compose'),',')[0]

split(Outputs('Compose'),',')[1]

split(Outputs('Compose'),',')[2] .... etc.

I am new to this and I know I am missing something but I don't know what I don't know.

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (1)

Sort by

rzaneti 4,483 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi @Nanoo51969 ,

I'm not sure if I understood correctly where is your issue. I've tested with the same expression that you used and it worked fine (I've just included a trim to remove any additional spaces at the beginning and at the end of the strings):

Each compose will will have one of the strings, and then you can add them (as dynamic content) to an Excel table by using the 'Add row into a table' action.

If this is not exactly what you need, please bring more details about the problem and which is the expected output.

Also, I'm sharing these links related to the topic to bring some additional context about how to work with text expressions in Power Automate and integrate it with Excel:

- Extracting and cleaning texts with Power Automate: http://digitalmill.net/2023/08/12/how-to-extract-and-clean-texts-with-power-automate/
- Inserting data into Excel tables http://digitalmill.net/2023/07/24/inserting-and-updating-data-into-excel-tables-with-power-automate/

Was this reply helpful? Yes No
Nanoo51969 8 on at

Like (0)

Report
Copy link

Link copied!

Thank you. Where I am having the problem is that it is not writing the split string back into the next excel column.
As you mention above, you can "add a row to the table" but I do not want to add a row.
I need a "text to columns" type of function where the 1st selection is in column k, the second selection in column L the next in column M, the next in column N and so on.

Was this reply helpful? Yes No
Verified answer

grantjenkins 11,063 Moderator on at

Like (0)

Report
Copy link

Link copied!
Can you clarify a few things?

Will there already be a header for each of these values in your Excel Table?

Do they need to just be added across the columns in the order they come through as, or would they need to always be in the same order?

Is this the output you would be expecting:

Was this reply helpful? Yes No
Nanoo51969 8 on at

Like (1)

Report
Copy link

Link copied!

Hi Grantjenkins,
This is a ranking question so the importance will differ depending on who is filling out the survey.
The Column Headings are Rank1, Rank2, Rank3 etc.
But.... I think I figured it out. I was missing an Update A Row for Excel. Uhg.
Also did not know how to find the results of the split (if the function was working) until the last response so went in and figured out how to check that too.
Thank you everyone for your help.

Was this reply helpful? Yes No