Clever Geek Handbook

SQL Server for Excel 2007 - New Lines

I am trying to fetch data from SQL Server 2000 and put into Excel. Which sounds simple, I know. I am currently copying and pasting to Excel from Management Studio

The problem is that one of the columns is the address and it doesn't store the newlines. These new lines must stay in the same cell in excel, IE cannot span 3 lines, for 3 lines of address.

In SQL Data CHAR (10) and CHAR (13) are included and other software picks them up correctly.

EDIT: Sorry I forgot about this, I want rows to be present in a cell, but not span multiple cells.

+1


source to share


2 answers


Try running this macro on a worksheet. (Right click on the worksheet tab and click View Code to bring up the VB IDE.)



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


source


For some reason, Excel seems to use these symbols, the other way around:



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


source






More articles:


All Articles