Carraige return in a formula in Excel 2010

[spthomas]

I need to put text in a cell that has carriage returns in it. What I'm trying to insert is like this:

<TABLE style="WIDTH: 346pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=461 border=0><COLGROUP><COL style="WIDTH: 346pt; mso-width-source: userset; mso-width-alt: 16859" width=461><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 346pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=461 height=20>DESCRIPTION:
(put a description here)
</TD></TR><TR style="HEIGHT: 60pt" height=80><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 346pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 60pt; BACKGROUND-COLOR: transparent" width=461 height=80>SOURCE DATABASE:
SOURCE TABLE:
SOURCE COLUMN:

The formula I've used before for this is:

=$E$224&(CHR(10))&B224&(CHR(10))&$E$225

Where the first line is in cell $E$224 as a constant reference to that one cell where the text string is.

The variable descriptions would appear on the second line and would vary (I copy/paste the formula down a column) and in this example is in B224

Finally, the other three lines of boilerplate are in cell $E$225, another reference to the text value in that one cell.

When I put in various combinations of the cell references it's fine. I even tried putting in constant strings into the formula, and they do fine. But everytime I use the CHR(10) to get a carriage return, the formula errors with "unrecognized character". And in every case if I remove the CHR(10) it works, just no carriage returns.

I know I've used this formula before in Excel 2003. But in Excel 2010 it doesn't seem to work. What changed?

Steve
</TD></TR></TBODY></TABLE>

 

[VoG]

Try

=$E$224&CHAR(10)&B224&CHAR(10)&$E$225

 

[spthomas]

Curiouser and Curiouser. When I use that string, in the cell itself I see this:

DESCRIPTION: ??SOURCE DATABASE: ?SOURCE TABLE: ?SOURCE COLUMN: ?

(but instead of a normal ? symbol, its a symbol with a question mark in a box.)

But when I copy and paste it to another cell, I see the correct result:


<TABLE style="WIDTH: 407pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=543 border=0><COLGROUP><COL style="WIDTH: 407pt; mso-width-source: userset; mso-width-alt: 19858" width=543><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 407pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=543 height=20>DESCRIPTION:

SOURCE DATABASE:
SOURCE TABLE:
SOURCE COLUMN:
</TD></TR></TBODY></TABLE>
So is there something in how this is set up that I'm viewing the result cells wrong?

And I looked in some Microsoft docs and it showed CHR(10) not CHAR(10). Is that a VB reference or something?

thanks, we're getting closer!

Steve

 

[Greg Truby]

Try checking to see if the Wrap Text property is turned on the cell that's misbehaving. And yes, CHAR is the worksheet formula. CHR is the VB function.

 

[spthomas]

That was it! I clicked the "Wrap Text" button to turn it on, and no more funny qustion-mark-in-a-box symbols, it's all working fine.

Thanks a lot!

 

[Anne Troy]

Very helpful, guys! Thanks!