Combining multiple cell values with "hard" carriage return separating each

[Bill Y]

Hello,

I have several values in separate cells that I would like to combine into a
single cell with a carriage return (Alt-Enter) separating each value. I
basically want to take a column of values and turn it into a text file with
the values each on their own line. I learned that Chr(10) is a line feed.

(A1 =) 'line one text
(A2 =) 'line two text

What I want is for A3 to contain:

(A3 =) line one text
line two ext

That is, I want the values of A1 and A2 combined as if I typed them in and
separated them with Alt-Enter.

I tried a direct formula in the destination cell:

(A3 =) = A1 & Chr(10) & A2
Result: #NAME?

I guess you can only use Chr(10) in a macro

So I wrote a macro:

Sub combine()

ActiveCell.FormulaR1C1 = "=R[-2]C" & Chr(10) & "=R[-1]C"

End Sub

Result: FALSE
In the formula line:

(A3 =) =A1
=A2

Does anyone have any other ideas?

Bill

 

[Frank Kabel]

Hi Bill
try CHAR() instead of Chr():
= A1 & CHAR(10) & A2

HTH
Frank

 

[Dave Peterson]

You won't want to use this, but you could change your macro to:

ActiveCell.FormulaR1C1 = "=R[-2]C" & "&""" & Chr(10) & """&" & "R[-1]C"

Remember to enable text wrapping for that cell.

Hello,

I have several values in separate cells that I would like to combine into a
single cell with a carriage return (Alt-Enter) separating each value. I
basically want to take a column of values and turn it into a text file with
the values each on their own line. I learned that Chr(10) is a line feed.

(A1 =) 'line one text
(A2 =) 'line two text

What I want is for A3 to contain:

(A3 =) line one text
line two ext

That is, I want the values of A1 and A2 combined as if I typed them in and
separated them with Alt-Enter.

I tried a direct formula in the destination cell:

(A3 =) = A1 & Chr(10) & A2
Result: #NAME?

I guess you can only use Chr(10) in a macro

So I wrote a macro:

Sub combine()

ActiveCell.FormulaR1C1 = "=R[-2]C" & Chr(10) & "=R[-1]C"

End Sub

Result: FALSE
In the formula line:

(A3 =) =A1
=A2

Does anyone have any other ideas?

Bill

 

[Bill Y]

Goodness. Thanks.