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.

Answer 1

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

Answer 2

For some reason, Excel seems to use those characters the other way around, that is:

"a" & Chr(13) + Chr(10) & "b"