Answered

Pasting complex Excel Table to a text input

(0) Share

Report

Posted on by WorkingRicardo

860

With the code below I managed to grab the data of a copy pasted Excel sheet and then "transfer" all data of a column and put it in different text labels:

With(
    {
        words: Split(Substitute(Trim(DataCardValue2.Text); Char(10); " "); " ");
        numCols: 4;
        numRows: CountRows(
            Split(Substitute(Trim(DataCardValue2.Text); Char(10); " "); " ")
        ) / 4
    };
    Concat(
        Sequence(numRows) As rowIndex;
        Last(
            FirstN(
                LastN(
                    words;
                    CountRows(words) - ((rowIndex.Value - 1) * numCols)
                );
                2   // column2 
            )
        ).Value & " " & Char(10)
    )
)

For example: from this table where i select only the data withouth the headers

If I copy into my app will look like this:

Meaning I managed to grab all data from the second column.

This works fine, but it hadn't ocurred to me that the user input won't look like this. It will be something more like this:

if i try selecting and copying it will look like this:

So spliting the data on " " is not a nice solution as this will produce wrong results in this case where user submited "XX1 // XX2".

Is there a way then so that I can obtain the data from the column?

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Chriddle 8,638 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

The separator for Excel data cells from the clipboard is the <tab> character - char(9).
At least here ;)

Was this reply helpful? Yes No
Chriddle 8,638 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!

Was this reply helpful? Yes No
Verified answer

Kalathiya 1,510 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!
Hello @WorkingRicardo,

Could you please try below:

Concat( Split(DataCardValue2.Text; Char(10)) As RowData; If( CountRows(Split(RowData.Value; Char(9))) >= 2; Last( FirstN( Split(RowData.Value; Char(9)); 2 ) ).Value & Char(10) ) )

If this response resolves your issue, please mark it as the Verified Answer so it can help other community members as well.

---------------------------------------------------------------------------------

📩 Need more help? Mention @Kalathiya anytime!
✔️ Don’t forget to Accept as Solution if this guidance worked for you.
💛 Your Like motivates me to keep helping!

Was this reply helpful? Yes No
WorkingRicardo 860 on at

Like (0)

Report
Copy link

Link copied!
@Kalathiya @Chriddle Hey guys thanks for your replies on this!

I ended up using Kala's code for my scenario so I'll chose that as an answer. But chriddle's take was great as well!

I changed up the code a bit as well as I also need this to be in a text input and each cell of the Excel has to be separated by a space (" ")

Concat( Split( TrimEnds(DataCardValue2.Text); Char(10) ) As RowData; With( { cols: Split(RowData.Value; Char(9)) }; If( CountRows(cols) >= 2; Last( FirstN(cols; 2) ).Value ) ); " " )

Was this reply helpful? Yes No