Handle pasted text from Excel, and split it into arrays of rows and columns

paste_excel.js

var clipboardData = window.clipboardData || event.clipboardData || event.originalEvent && event.originalEvent.clipboardData;

var pastedText = clipboardData.getData("Text") || clipboardData.getData("text/plain");

if (!pastedText && pastedText.length) {
    return;
}

// Parse the pasted text from Excel into rows.
// Pasted text is usually separated by a new line for each row,
// but a single cell can contain multiple lines, which is what
// we pars out in the first `replace`.
//
// We find all text within double-quotes ('"') which has new
// lines, and put the text within the quotes into capture
// groups. For each match, we replace its contents again, by
// removing the new lines with spaces.
//
// Then lastly, once we've joined all the multi line cells, we
// split the entire pasted content on new lines, which gives
// us an array of each row.
//
// Since Windows usually uses weird line-endings, we need to
// ensure we check for each of the different possible
// line-endings in every regexp.
//
// It also handles cells which contains quotes. There appears
// to be two ways this is handled. In Google Docs, quotes within
// cells are always doubled up when pasting, so " becomes "".
// In Libre Office, the quotes are not normal quotes, some
// other character is used, so we don't need to handle it any
// differently.
var rows = pastedText.replace(/"((?:[^"]*(?:\r\n|\n\r|\n|\r))+[^"]+)"/mg, function (match, p1) {
    // This function runs for each cell with multi lined text.
    return p1
        // Replace any double double-quotes with a single
        // double-quote
        .replace(/""/g, '"')
        // Replace all new lines with spaces.
        .replace(/\r\n|\n\r|\n|\r/g, ' ')
    ;
})
// Split each line into rows
.split(/\r\n|\n\r|\n|\r/g);

Thanks Bjåen. This code is beautiful.

Thank you, that's what I'm looking for

Life saver! Used this to get ChatGPT responses on different rows. Amazing :) Thanks!

Thanks. It's useful in most cases. However, it doesn't work if a cell contains single-line data with a tab character, such as the following data:

	"test	"	

The above code represents a 1-row, 3-column data. The first and the last cells are empty, and the second cell contains test .