I am currently dealing with an issue of copying multiple rows of one column from Excel (on macOS) to the browser. I am receiving an event and access the copied content like this: event.clipboardData.getData('text/plain');

After I received the content, I want to split them with various matchers like this:

const separators = [',', ';', '\\(', '\\)', '\\*', '/', ':', '\\?', '\n', '\t'];
return data.split(new RegExp(separators.join('|'), 'g')).map(d => d.trim());

This works perfectly in Firefox but it does not in latest Chrome and Safari. I thought, you would match new rows with \n or \t. My goal is to retrieve an array of values per line. I imagine this has something to do with special line endings of Excel because when using Numbers from Apple everything works perfectly.

Any help really appreciated. Thanks in advance!

I am currently dealing with an issue of copying multiple rows of one column from Excel (on macOS) to the browser. I am receiving an event and access the copied content like this: event.clipboardData.getData('text/plain');

After I received the content, I want to split them with various matchers like this:

const separators = [',', ';', '\\(', '\\)', '\\*', '/', ':', '\\?', '\n', '\t'];
return data.split(new RegExp(separators.join('|'), 'g')).map(d => d.trim());

This works perfectly in Firefox but it does not in latest Chrome and Safari. I thought, you would match new rows with \n or \t. My goal is to retrieve an array of values per line. I imagine this has something to do with special line endings of Excel because when using Numbers from Apple everything works perfectly.

Any help really appreciated. Thanks in advance!

• javascript
• regex
• excel
• google-chrome
• safari

2 Answers 2

All you need is to add a CR, carriage return, symbol (it is the default line break style in MS Office documents). Also, String#split method does not require the use of the g global modifier with the regex passed as an argument, as this method behavior is like that by default.

Use

const separators = [',', ';', '\\(', '\\)', '\\*', '/', ':', '\\?', '\n', '\r'];
return data.split(new RegExp(separators.join('|'))).map(d => d.trim());

Okay, I got this working.

    /*
     * Chrome and Safari seem to treat new lines pasted from Excel like an Enter press.
     * Enter has a CharCode of 13, so checking if this string contains CharCode 13 is sufficient.
     * If the string contains a CharCode 13, we split the string after every CharCode 13.
     * And kids, that's how I made pasting from Excel possible (HIMYM voice)
     */

    const enterCharCode = String.fromCharCode(13);

    if (data.includes(enterCharCode)) {
        return data.split(enterCharCode);
    }

    const separators = [',', ';', '\\(', '\\)', '\\*', '/', ':', '\\?', '\n'];
    return data.split(new RegExp(separators.join('|'), 'g')).map(d => d.trim());