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

splitting text (from form submission) into rows and then columns

HI All, 

 

I have a form entry that I need to split into rows and then columns based on delimiters. 

 

Text :

///*no:    *Model:    *S/N:   *Fault:   *Required:
///*no:    *Model:    *S/N:   *Fault:  *Required:
///*no:    *Model:    *S/N:   *Fault:   *Required:
///*no:    *Model:    *S/N:   *Fault:   *Required:
///*no:    *Model:    *S/N:   *Fault:   *Required:
///*no:    *Model:    *S/N:   *Fault:   *Required:

 

Row Delimiter : /// 

Column delimiter: *

 

The user will type notes after each heading so it will not come through in neat rows as shown above. 

 

Is this possible ? 

 

My initial thought was that I would need to split the data and then split it again but I have hit a dead end when trying to apply this to a table in excel. 

 

splitsplit.JPG

 

set variable : 

split(body('Get_response_details')?['r4d623ac9eaa040c7a2a0a137b260ce45'],'///')
 
Set variable 2:
split(variables('Taskissues'),'*')
 
The desired output into the table would be :
no:Model:  S/N:  Fault:Required:
no:Model:  S/N:  Fault:Required:
no:Model:  S/N:  Fault:Required:
no:Model:  S/N:  Fault:Required:
no:Model:  S/N:  Fault:Required:

 

Although failing that : 

*no:    *Model:    *S/N:   *Fault:   *Required:
*no:    *Model:    *S/N:   *Fault:   *Required:
*no:    *Model:    *S/N:   *Fault:   *Required:
*no:    *Model:    *S/N:   *Fault:   *Required:
*no:    *Model:    *S/N:   *Fault:   *Required:
*no:    *Model:    *S/N:   *Fault:   *Required:

 

Its worth noting that the text may not be split into single lines when live responses are received and the No. refers to an asset number not a unique ID for the row. 

 

I am the author of the form so I can change the formatting, delimiters and headings if it would make the splitting easier. 

5 REPLIES 5
Highlighted
Super User II
Super User II

Re: splitting text (from form submission) into rows and then columns

Hello @MattMannion 

 

When you perform the first split then your first variable, which is an array, now contains several items. In order for you to perform the second split, you will have to loop through each item in the array.

Example (after your first split):

Apply to each action on the array variable

  • Inside the Apply to each perform the second split on the current item
  • Store the content of the second split as needed

Hope the above helps you on the way 🙂

Kind regards, John

Highlighted
Frequent Visitor

Re: splitting text (from form submission) into rows and then columns

Thank you for the swift reply, I will try this now. 

Highlighted
Frequent Visitor

Re: splitting text (from form submission) into rows and then columns

Hi @JohnAageAnderse

 

I am not sure I have understood your advice. 

 

Set variable 1: 

split(body('Get_response_details')?['r4d623ac9eaa040c7a2a0a137b260ce45'],'///')

 

Set variable 2: 

Current Item is not an option, So I selected 'Taskissues' again but this returns the following error 

 

splitsplit1.JPG

Highlighted
Super User II
Super User II

Re: splitting text (from form submission) into rows and then columns

Hello @MattMannion 

 

If there is not shown the "Current item" then you have to use an expression instead for the current item item()

Kind regards, John

Highlighted
Microsoft
Microsoft

Re: splitting text (from form submission) into rows and then columns

@JohnAageAnderse 's advice is very pertinent.

Helpful resources

Announcements
firstImage

Super User Program Update

Three Super User rank tiers have been launched!

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

Join the new Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Top Solution Authors
Top Kudoed Authors
Users online (6,431)