Match & MatchAll to parse Excel cut & paste text

[AlexLindberg]

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:

C1	C2	C3
No NewLIne	New 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.

[RandyHayes · Accepted solution]

@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.

[AlexLindberg]

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.

[AlexLindberg]

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.