Export to Excel and non-breaking spaces

I'm seeing something curious that has caused us some issues. It appears that when there is more than one adjacent space in a value, then in Excel, some of the spaces will be converted to non-breaking spaces (ASCII A0). In Rich Text mode, when the spaces are embedded in a string, then all but the last is converted to an NBSP. If the spaces are at the end of the string, then all are converted to an NBSP. In the case of a single trailing space, it looks like that space is stripped.

I see slightly different behavior if using 'Open in Excel' in Data Grid mode. There, all but the first are converted to NBSP, including trailing spaces.

On the other hand, If I copy and paste the values straight out of the Results (Rich Text or Data Grid), it looks like all the original spaces are intact. 'Export to CSV file' in the grid context menu seems okay, too.

I don't know how to tell whether LINQPad or Excel is performing the unwanted translation.

This is a problem because we have a workflow built around export to Excel, edit, convert to CSV, and upload into third-party software. Unfortunately, the grid Export to CSV doesn't work as a replacement because Excel makes other default translations when opening a CSV file that are even more troublesome.

Any insights?

LINQPad 5.41 and Excel 2013.

Sign In or Register to comment.