SQL Server to Excel 2007 - New Lines

Question

I'm trying to retrieve data from an SQL Server 2000 server, and place into Excel. Which sounds simple I know. I'm currently Copying, and Pasting into Excel, from Management Studio

The problem is one of the columns is an address, and it’s not retaining the newlines. These new lines have to stay in the same cell in excel, I.E cannot take up 3 rows, for 3 lines of an address.

In the SQL Data CHAR(10) and CHAR(13) are included, and other software pick up on these correctly.

EDIT: Sorry I forgot to metion, I want the lines to be present in the cell, but not span multiple cells.

If you go to pasted-in address cell and press F2 then ENTER, do you get the result you want? — Tmdean

Tmdean Tmdean · Accepted Answer · 2008-12-11T21:01:32

Try running this macro on the worksheet. (Right click the worksheet tab and click "View Code" to summon the VB IDE.)

Sub FixNewlines()
    For Each Cell In UsedRange
        Cell.FormulaR1C1 = Replace(Cell.FormulaR1C1, Chr(13), "")
    Next Cell
End Sub

SQL Server to Excel 2007 - New Lines

2 Answers