12

How do you enter a carriage return or CHAR(13) in a worksheet cell manually ? I'm not talking about a line feed or CHAR(10).

I've seen this in a file and the carriage return had a very weird effect on the cell contents, but I can't reproduce it manually.

UPDATE

There are very good answers about how to enter a line feed in an Excel worksheet cell, and actually one of them gave me an idea how to achieve what I wanted in the first place:

=A1&CHAR(13)&A2 'Then copy cell + paste values only back again.

I'll accept the one that updates their content to reflect this. However, it'd be nice to know whether there's an equivalent of Alt + Return to type a carriage return.

Improve this question
1
  • Another thing that doesn't work, at least in Excel 2013 on Windows: pasting the CR character. Excel (or perhaps Windows) turns the CR into an LF.
    – Mathieu K.
    Apr 25, 2019 at 4:55

2 Answers 2

Reset to default
24

Found here:

Method 1 - Cell Wrapping

Often you only need to set the cell to wrap text, and you can set the width of the cell to whatever is desired. Choose Format Cells, Alignment tab, and check Wrap text.

alt text

The result is:

alt text

Method 2 - Insert a Return

This one's a no-brainer. Just type the first line, hit Alt + Enter and type the second line. The result is virtually the same as above, however, if you copy and paste this to Word, for instance, you'll end up with a line break. Or if you export to CSV or other text format, you may get unexpected results.

alt text

The result will be:

alt text

Method 3 - Using a Formula

To use this, you must have wrap text selected. Here's a sample formula:

=A1 & char(13) & A2

Then copy the cell and paste the values only back again.

The results, again, are the same as above. However, if you forget to wrap text on the cell, you'll see this:

alt text

Improve this answer
3
  • 2
    In method 3 he does &char(10)& which is a line feed char(13) is a carriage return which is what's specified in the question see en.wikipedia.org/wiki/Ascii#ASCII_control_characters
    – Col
    Sep 24, 2009 at 11:30
  • 1
    Please note that the only correct answer is Method 3 - Using a formula as indicated in the original question.
    – guillermooo
    Sep 25, 2009 at 10:27
  • As per my test (image here), Methods 1 and 2 are just plain wrong. Method 1 inserts no line break characters of any kind. Method 2 inserts a linefeed (character 10), which is explicitly not what the question asks for. Even Method 3 is still slightly wrong: the picture still incorrectly shows CHAR(10); and, in at least my Excel 2013, the carriage return doesn't cause the text to wrap, so whether Wrap Text is turned on is immaterial and so should be omitted from the instructions. Please see/edit my CW instead.
    – Mathieu K.
    Apr 25, 2019 at 4:26
0

As per the OP's edit to the original question, use a formula that uses concatenation and ASCII character 13. Copy it and paste it, values-only, over itself.

Sample formula:

="start of text"&CHAR(13)&"continuation of text"

or

=A1&CHAR(13)&A2
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.