[SOLVED] Line Split Issues when Copying Data from SQL Server to Excel


Recently while running a query in SQL Server Management Studio I needed to export the data to copy and paste the results to Microsoft Excel, there was a row with free text, while pasting it into Excel one row would get split into many rows instead of one row.


The reason this issue occurs is due to the carriage return (\n\r) wasn't taken into consideration when copying data from SQL Server to Excel. This behaviour is different in SQL Server 2012, because the carriage return (\n\r) are preserved, therefore splitting the row into multiple rows in Excel when pasting. The fix is to remove the carriage return (char(13)) and line feed (char(10)) within you output.

SELECT Column1, replace(replace(Column2FreeText, char(10), ''), char(13), ''), Column3
from YourTable