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

Match & MatchAll to parse Excel cut & paste text

I am trying to create a simple app that will take a cut from an Excel spreadsheet (more that one column and row) and parse it into a gallery.  It is simple to do "IF" there are no NewLine (\n or Char(10)) in a single cell.  In these cases the cell's text is enclosed in quotes.  Each cell is separated by Tab (\t) characters.

 

There are several examples on how to do the simple cases where the only NewLine is the end of the row.

 

In Googling for a RegEx to ignore NewLines when inside quotes -

(?<=^[^"]*(?:"[^"]*"[^"]*)*)\r?\n

The issue here is that 1) Match or MatchAll does not allow dynamic RegEx expressions and 2) I can't figure out how to escape the quote marks in a string.  The 'Standard' method is to do something line \\" but this syntax does not work.

 

As an example.  Column C2 has an included NewLine:

C1C2C3
No NewLIneNew Line
2nd line
C3 without New line

 

This is what is pasted.  Note the quotes around the embedded newline

C1 C2 C3
No NewLIne "New Line
2nd line" C3 without New line

 

Any Ideas?

 

Thank you for your time.

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@AlexLindberg 

I will start you out by hopefully saving you some time on the base app - the copy/paste from Excel into your app.

If you take a look at this post, you will see that I put this together for the person posting that question.

In message #14, you will see the bulk of what is going on, including a visual of the app in action.

 

Also, this was rehashed for another post that is worth reviewing. Particularly message #11 of that thread where I supply a complete sample app.

 

NOW, that said, I am not sure how it responds to the new line in a cell.  I will actually test that now, but it shouldn't be difficult to change to accommodate it.

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

3 REPLIES 3
RandyHayes
Super User
Super User

@AlexLindberg 

I will start you out by hopefully saving you some time on the base app - the copy/paste from Excel into your app.

If you take a look at this post, you will see that I put this together for the person posting that question.

In message #14, you will see the bulk of what is going on, including a visual of the app in action.

 

Also, this was rehashed for another post that is worth reviewing. Particularly message #11 of that thread where I supply a complete sample app.

 

NOW, that said, I am not sure how it responds to the new line in a cell.  I will actually test that now, but it shouldn't be difficult to change to accommodate it.

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
AlexLindberg
Frequent Visitor

If there are no embedded CR/LF (Char(10)) in the copied data then using Split(Text.Text, Char(10)) work just fine to deliver a row.  Then using RegEx to split on Tabs to get each cell is straight forward.

 

However if there are embedded CR/LF in a cell then the split will not work as expected.  If Split has an option to ignore  CR/LF incased in Quotes “\n” then all would work as expected.

 

I look forward to your investigation.

Genius - Using HTML tags (</tr>) to spilt the row and </td> to split the cells removes the CR/LF and the paragraph tags <br> & <p></p> from consideration.  Very Cool.

 

Thanks.

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors
Top Kudoed Authors
Users online (1,275)