Preserving line breaks when referring to text in another cell

Hello,

I've got an odd problem which I would appreciate any advice on.

I have some text in a cell, which breaks over several lines, separated by ALT+ENTER (i.e. an ANSI character 10). I am trying to refer to that cell in another cell (e.g. by putting in cell B1 the formula '=A1' where A1 contains the text). It works, except that the line breaks are lost and the text all comes out on one line. If you go through the text character by character, the ANSI code 10 characters are still there, but for some reason they don't cause a line break in that cell as they do in the original. I have even tried using SUBSTITUTE to replace them all with 13's, but that doesn't help at all.

Many thanks for any pointers!

All best wishes,

James.

Originally Posted by JamesG28

Hello,

I've got an odd problem which I would appreciate any advice on.

I have some text in a cell, which breaks over several lines, separated by ALT+ENTER (i.e. an ANSI character 10). I am trying to refer to that cell in another cell (e.g. by putting in cell B1 the formula '=A1' where A1 contains the text). It works, except that the line breaks are lost and the text all comes out on one line. If you go through the text character by character, the ANSI code 10 characters are still there, but for some reason they don't cause a line break in that cell as they do in the original. I have even tried using SUBSTITUTE to replace them all with 13's, but that doesn't help at all.

Many thanks for any pointers!

All best wishes,

James.

Hi
right click on that cell, select Formate cells, under Alignment tab check Wrap text and press Ok
then adjust col width accordingly

Regards.

Many thanks, Starguy, but no joy I am afraid. The problem doesn't seem to be a formatting issue per se. In the original cell, there are a number of forced line breaks, generated by typing ALT+ENTER within the entry bar at the top of the sheet. As a result, the text occupies several lines, with anything from a few characters to a few words on each line. When I refer to that cell in another cell, those forced line breaks don't come across. The text occupies a single line, or breaks in different places if I turn on wordwrap and shrink the column. The actual ANSI line break character does come across, it is a CHAR(10), but it doesn't appear to have the same effect in the destination cell as it does in the source cell.

Incidentially, I am using Excel 2007 B2TR - I don't know if the same thing occurs in other versions of the software.

Best wishes,

James.

Originally Posted by oldchippy

Here's a way to keep the line breaks, use your formula = A1, then use the Format Paintbrush, click on A1, then on your formula cell.

Hi oldchippy

this will result in same thing i-e wrapping the text.
if you see the format of A1 (which is with line breaks you will see wrap text option checked) and when you use Paint Brush it will also copy the format i-e wrap text will be checked in other cell as well.

Success! Somehow, using the format paintbrush worked when formatting the cell itself didn't. Strange, but effective. Many thanks for all the help, guys!

James