Insert line break into SQL Query embedded in Excel Workbook Connection

Question

Using: t-SQL, SSMS 2008, Excel 2010

Background: I have a SQL query that I've embedded into an Excel workbook via Connection that the end user can refresh. This query returns a column with a long string of sentences, basically a paragraph (this column's datatype is flexible).

What I Am Trying To Do: I would like each sentence of the paragraph to start on a new line in the Excel cell that it's in.

Example of UNDESIRED formatting:

Example of DESIRED formatting:

What I've Tried: I've tried adding the CHAR(13) linebreak to the SQL query. It correctly prints the paragraph with the desired line breaks in SSMS, but it doesn't transfer over to Excel, when the workbook is refreshed.

IS the excel document formatted to allow wrapping of text? and do you need char(10)+char(13)? (vbcrlf) (carrige return line feed) — xQbert
– xQbert, Commented May 31, 2016 at 18:00
@xQbert Let me try each of these now, and I will report back — Chase
– Chase, Commented May 31, 2016 at 18:01
@xQbert Looks like that was it! It was a combination of both enabling text wrapping and adding CHAR(10) to the CHAR(13) that I already had. If you'd like to make your comment an answer, I'd be happy to accept. — Chase
– Chase, Commented May 31, 2016 at 18:04

xQbert · Accepted Answer · 2016-05-31 18:05:42Z

2

I'm not sure if you really need the wrapping of text. But I'm pretty sure you need both char(10) and char(13) carriage return and line feed (vbcrlf) equivalent.

answered May 31, 2016 at 18:05

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Chase Over a year ago

Thank you much for your answer!! Btw, I tested it without wrapping and it did NOT work.

Chase Over a year ago

For anyone who reads this answer, here is an example of how it will work, assuming you have text wrap enabled in Excel: select 'This is a paragraph.' + CHAR(10) + CHAR(13) + 'It contains many sentences.' + CHAR(10) + CHAR(13) + 'I would like each sentence to start on a new line w/in the cell.'

Collectives™ on Stack Overflow

Insert line break into SQL Query embedded in Excel Workbook Connection

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related