2

I am trying to combine three cells into one, with Alt + Enter or Char(10) separating each value. When I use a simple Excel formula =B2 & Char(10) & C2 & Char(10) & D2 and wrap the text of the destination cell, everything seems normal. The actual cell value is a string of B2, C2, and D2 run together. Is there actually a way to have the value appear as if I manually entered First Value Alt + Enter Second Value Alt + Enter Third Value?

6
  • 1
    Sorry, but it's a little unclear what you're asking for. Do you want it to show that way without turning on Word Wrap? Or do you want it not to show up as a formula in the formula bar? Or something else?
    – Excellll
    Apr 11, 2014 at 13:39
  • If you want space between the entries, just add & " " & to your formula
    – user304064
    Apr 11, 2014 at 13:42
  • Alt+Enter produces an in-cell line break. I'm guessing that OP wants to concatenate values but separate them using such an in-cell line break. I don't know how to create an in-cell line break in a formula, otherwise I'd have posted an answer. Apr 11, 2014 at 13:54
  • @CJK: The formula you posted works for me. However, have you turned on Word Wrap in the formula cell?
    – Madball73
    Apr 11, 2014 at 14:03
  • possible duplicate of How to enter a carriage return in an Excel file
    – LPChip
    Apr 11, 2014 at 14:12

2 Answers 2

1

Easy!

Once you have entered the formula and see the displayed value looks O.K., copy the cell and PasteSpecialValues back into the cell.

The formula will be gone and the contents will appear to have been entered manually!

Or you may also paste special values with source formatting and you get the desired results

1

You can get it to work using CHAR(13) instead of CHAR(10):

=CONCATENATE(A9&CHAR(13),A11&CHAR(13),A13)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .