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

How to split a single-column text string into multiple columns

Hi, basically I have a column('Answer') in a collection('QnList') that has to be uploaded onto sharepoint, but transposed into a row, with each entry under one column. In the 'Answer' collection it looks like:

    Answer

1.1_false

1.2_true

1.3_true

What I managed to work out is to concatenate the column 'Answer' separated with commas into a string, then upload this onto the SP list via a form. This leaves me with one column and one row with all the 'Answer' records concatenated into a string. On the SP List it looks something like this:

                       col1

row 1 1.1_false,1.2_true,1.3_true,1.4_false,.....

What I need help with now is to split this string into columns while remaining on the same row, which is why I do not think Split() will work in this case. Ideally what i am hoping to get is:

            col1         col2        col3         col4 

row1 1.1_false  1.2_true  1.3_true  1.4_false .....

Can this be done?

1 ACCEPTED SOLUTION

Accepted Solutions

Have you tried updating the SPList with Patch() and using the Lookup() to assign the correct values to the SPColumns.

 

Here is an example of what it might look like:

 

 

Patch(
    SPList,
    Defaults(SPList),
    {
        SPColumnName1: LookUp(QnList, StartsWith(Answer,"1.1"),Answer),
        SPColumnName2: LookUp(QnList, StartsWith(Answer,"1.2"),Answer),
        SPColumnName3: LookUp(QnList, StartsWith(Answer,"1.3"),Answer)
    }
)

 

 



--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

View solution in original post

4 REPLIES 4
Super User III
Super User III

Hi @gloomypika 

Would you know beforehand how many rows you want to transpose? Because if so, that would make this task easier.

I am building 2 similar checklist apps, so every row corresponds to a question in the checklist. I am currently focusing on the shorter checklist of 19 qns/rows but the 2nd larger checklist will have about 70 qns/rows. Any suggestions?

I am also thinking of using a addcolumn and a trim to do this.

Have you tried updating the SPList with Patch() and using the Lookup() to assign the correct values to the SPColumns.

 

Here is an example of what it might look like:

 

 

Patch(
    SPList,
    Defaults(SPList),
    {
        SPColumnName1: LookUp(QnList, StartsWith(Answer,"1.1"),Answer),
        SPColumnName2: LookUp(QnList, StartsWith(Answer,"1.2"),Answer),
        SPColumnName3: LookUp(QnList, StartsWith(Answer,"1.3"),Answer)
    }
)

 

 



--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

View solution in original post

Now i know why knowing how many rows i have beforehand makes this easier and also, i can easily add new/remove columns should there be any changes to my checklist data, thanks!!!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (10,882)