Excel - 3 cells into 1 cell

[Gene]

Hello!
I have 3 cells of data:
a1 = car
a2 = truck
a3 = cycle
Is there a way I can "copy" and "paste" these 3 cells into 1 cell, without
the loss of data (merge cells of course does not work). I would like to end
up with:
b1 = car
truck
cycle

I'm using Excel 2003

THANKS!!!!!
Gene

 

[Luke M]

=A1&CHAR(10)&A2&CHAR(10)&A3

Make sure you format the cell to have word wrap, otherwise the new line
character will just display a square.

 

[Peo Sjoblom]

Not by copying and pasting in one fell swoop.

One way would be to use a formula

=A1&CHAR(10)&A2&CHAR(10)&A3


make sure B1 has wrap text turned on under format>cells>alignment


--


Regards,


Peo Sjoblom

 

[Gene]

THANKS LUK!
U are the MAN!

=A1&CHAR(10)&A2&CHAR(10)&A3

Make sure you format the cell to have word wrap, otherwise the new line
character will just display a square.

 

[Gene]

THANKS Peo!!!!!!!!!!!!!!!!!

Not by copying and pasting in one fell swoop.

One way would be to use a formula

=A1&CHAR(10)&A2&CHAR(10)&A3


make sure B1 has wrap text turned on under format>cells>alignment


--


Regards,


Peo Sjoblom

 

[Gord Dibben]

One more using a UDF to save some typing.

Function ConCatRange(CellBlock As Range) As String
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.Text) > 0 Then sbuf = sbuf & Cell.Text & Chr(10)
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

=ConCatRange(A1:A3)

=ConCatRange(A1,A2,A3,F7,G12) for non-contiguous range.


Gord Dibben MS Excel MVP