Need to insert line-feed in Excel concatenation (VB)

[Guest]

I want to concatenate values from multiple cells into one cell. I want a
line-feed separating the values in the combined cell. I tried using the
ASCII character code (10 for linefeed, 13 for carriage return), but its not
working the way I did it.

Any help is greatly appreciated.

 

[Niek Otten]

="a"&CHAR(10)&"b"

Format>cells>Alignment, check Wrap text

 

[Chip Pearson]

Susan,

Try something like

=A1&CHAR(10)&A2

or, in VBA

Range("B1").Value = Range("A1").Value & Chr(10) &
Range("A2").Value

Ensure that the cell has Wrap Text enabled (Format menu, Cells,
Alignment tab).


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

Yes - the Chr(10) did it. Thanks very much!
Susan

 

[Guest]

I find that using vbLF is a bit more readible and is certainly faster to type
than CHR(10). Am I missing something here?

 

[NickHK]

vbLF, vbCR etc are certianly more clear to read, but have exactly the same
effect as Chr(10), Chr(13).
The only one that may make a difference is vbNewLine, which will give the
correct value on a PC or Mac.

NickHK

 

[Guest]

Hi Mr.Pearson,

a1&char(10)&a2 works well, but how to make the containing cell collapse
automatically when it contains multiple lines

Clara