Newline being ignored

I'm using Microsoft Query to retrieve data into Excel. I'm trying to build the SELECT statement so that it concatenates newline (carriage return) characters in the results, but the newline characters are being ignored. Yes, I have word wrap enabled, and the cell width is smaller than the data. I've tried multiple combinations of line feed (CHAR(10) or hex 0A) and form feed (CHAR(13) or hex 0D), but none of them work. I've tried all of these:

SELECT TRIM(cnam05) || X'0D' || X'0A' ||  
		TRIM(cad105) || X'0D' || X'0A' ||  
		TRIM(cad205) || X'0D' || X'0A' ||  
		TRIM(cad405) || X'0D' || X'0A' ||  
		TRIM(cad505) as "Customer Address",

SELECT TRIM(cnam05) || X'0A' || X'0D' ||  
		TRIM(cad105) || X'0A' || X'0D' ||  
		TRIM(cad205) || X'0A' || X'0D' ||  
		TRIM(cad405) || X'0A' || X'0D' ||  
		TRIM(cad505) as "Customer Address",

SELECT TRIM(cnam05) || X'0A' ||  
		TRIM(cad105) || X'0A' ||  
		TRIM(cad205) || X'0A' ||  
		TRIM(cad405) || X'0A' ||  
		TRIM(cad505) as "Customer Address",

SELECT TRIM(cnam05) || X'0D' ||  
		TRIM(cad105) || X'0D' ||  
		TRIM(cad205) || X'0D' ||  
		TRIM(cad405) || X'0D' ||  
		TRIM(cad505) as "Customer Address",

All of the above create the same results: a concatenated address, like this:

MY CUSTOMER123 MAIN STSOMETOWN NY

I need the results to look like this:

MY CUSTOMER
123 MAIN ST
SOMETOWN
NY

I can easily accomplish the above with a VBA macro. But I'd like to know how to resolve this "newline issue" with just a query.

Any suggestions?

Oh, and I've googled the hell out of this issue. I found lots of suggestions for Ctrl+Enter and formulas, but those don't apply to my issue.

TIA

Tom

What is the source database (i.e. what type of database is it, Access, Oracle, SQL, Excel Workbook etc)?

DB2 for i (IBM's silly name for the database on the AS400/iSeries)

Interesting. Either Excel version 1803 (from Office 365) behaves differently, or retrieving from DB2 instead of Excel behaves differently.

I changed my query to use CHR(13), just like your example, and I get the same results. Double-clicking in a cell merely puts me in Edit mode for the cell contents - it does not change or expand the newline characters.

What happens if you double click or go to Formula bar. And then remove focus (click out or tab out)?

This should be when CHR(13) is applied.

Same results - CHR(13) still does not get applied.

Hmm, are you able to do the job from DB2 side? Use job(s) to export to csv or some other file type. Then import the file into Excel.
This should accurately reflect carriage return/new line character.

If not, I'm out of ideas.

No, I'll just rewrite the process as a macro.

I was just hoping to find a workaround for another Excel oddity. It would be nice to be able to write queries that can combine address information into one cell. But since XL won't cooperate, I'll just write a macro.

Thanks for trying.

Tom