Hi i have 3 cells a1 a2 a3 all with about 2 sentences of data in, i
want to take all that data and put it in one cell, however i wish to
seperate each piece of data from each cell with a carrige line space
and title like below

go from this :

cell a1: helloe i am the data in a1
cell a2: hello i am the data in a2
cell a3: hello i am the data in a3

to this

cell b1: a1 data
hello i am the data in a1

a2 data
hello i am the data in a2

a3 data
hello i am the data in a3

hope this makes sense, been trying to solve this but cannot get the
line spaces or add the titles to each cell value

Although you posted in the programming newsgroup, you can do what you want
with a worksheet formula...

="a1 data"&CHAR(10)&A1&CHAR(10)&CHAR(10)&"a2
data"&CHAR(10)&A2&CHAR(10)&CHAR(10)&"a3 data"&CHAR(10)&A3

Rick


wrote in message
...
Hi i have 3 cells a1 a2 a3 all with about 2 sentences of data in, i
want to take all that data and put it in one cell, however i wish to
seperate each piece of data from each cell with a carrige line space
and title like below

go from this :

cell a1: helloe i am the data in a1
cell a2: hello i am the data in a2
cell a3: hello i am the data in a3

to this

cell b1: a1 data
hello i am the data in a1

a2 data
hello i am the data in a2

a3 data
hello i am the data in a3

hope this makes sense, been trying to solve this but cannot get the
line spaces or add the titles to each cell value

in B2:

=A1&CHR(10)&A2

then copy down

or simply:

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

On Jun 26, 9:42*am, "Rick Rothstein \(MVP - VB\)"
wrote:
Although you posted in the programming newsgroup, you can do what you want
with a worksheet formula...

="a1 data"&CHAR(10)&A1&CHAR(10)&CHAR(10)&"a2
data"&CHAR(10)&A2&CHAR(10)&CHAR(10)&"a3 data"&CHAR(10)&A3

Rick

wrote in message

...



Hi i have 3 cells a1 a2 a3 all with about 2 sentences of data in, i
want to take all that data and put it in one cell, however i wish to
seperate each piece of data from each cell with a carrige line space
and title like below

go from this :

cell a1: helloe i am the data in a1
cell a2: hello i am the data in a2
cell a3: hello i am the data in a3

to this

cell b1: a1 data
* * *hello i am the data in a1

* * *a2 data
* * *hello i am the data in a2

* * *a3 data
* * *hello i am the data in a3

hope this makes sense, been trying to solve this but cannot get the
line spaces or add the titles to each cell value- Hide quoted text -

- Show quoted text -

thanks,

I wanted to know if i could do it with a macro as the 3 cells i wish
to put together could be any where, i want to try and click on the
first of the 3 cells, press the button and them the cells join
together on another part of the spreadsheet,

And if you needed the code in a macro...

Sub CombineText()
With Worksheets("Sheet1")
.Range("B1").Cells.WrapText = True
.Range("B1").Value = "a1 data" & vbLf & .Range("A1") & _
vbLf & vbLf & _
"a2 data" & vbLf & .Range("A2") & _
vbLf & vbLf & _
"a3 data" & vbLf & .Range("A3")
End With
End Sub

By the way, notice that I take care of formatting B1 to wrap its text in the
code... don't forget that if you use my worksheet formula, you have to do
that manually.

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
Although you posted in the programming newsgroup, you can do what you want
with a worksheet formula...

="a1 data"&CHAR(10)&A1&CHAR(10)&CHAR(10)&"a2
data"&CHAR(10)&A2&CHAR(10)&CHAR(10)&"a3 data"&CHAR(10)&A3

Rick


wrote in message
...
Hi i have 3 cells a1 a2 a3 all with about 2 sentences of data in, i
want to take all that data and put it in one cell, however i wish to
seperate each piece of data from each cell with a carrige line space
and title like below

go from this :

cell a1: helloe i am the data in a1
cell a2: hello i am the data in a2
cell a3: hello i am the data in a3

to this

cell b1: a1 data
hello i am the data in a1

a2 data
hello i am the data in a2

a3 data
hello i am the data in a3

hope this makes sense, been trying to solve this but cannot get the
line spaces or add the titles to each cell value

This code will put the text from the active cell and the two cells below it
into B1 on Sheet1 (you can change destination cell in the With statement)..

Sub CombineText()
With Worksheets("Sheet1").Range("B1")
.Cells.WrapText = True
.Value = "a1 data" & vbLf & ActiveCell.Value & _
vbLf & vbLf & _
"a2 data" & vbLf & ActiveCell.Offset(1, 0).Value & _
vbLf & vbLf & _
"a3 data" & vbLf & ActiveCell.Offset(2, 0).Value
End With
End Sub

Rick


wrote in message
...
On Jun 26, 9:42 am, "Rick Rothstein \(MVP - VB\)"
wrote:
Although you posted in the programming newsgroup, you can do what you want
with a worksheet formula...

="a1 data"&CHAR(10)&A1&CHAR(10)&CHAR(10)&"a2
data"&CHAR(10)&A2&CHAR(10)&CHAR(10)&"a3 data"&CHAR(10)&A3

Rick

wrote in message

...



Hi i have 3 cells a1 a2 a3 all with about 2 sentences of data in, i
want to take all that data and put it in one cell, however i wish to
seperate each piece of data from each cell with a carrige line space
and title like below

go from this :

cell a1: helloe i am the data in a1
cell a2: hello i am the data in a2
cell a3: hello i am the data in a3

to this

cell b1: a1 data
hello i am the data in a1

a2 data
hello i am the data in a2

a3 data
hello i am the data in a3

hope this makes sense, been trying to solve this but cannot get the
line spaces or add the titles to each cell value- Hide quoted text -

- Show quoted text -

thanks,

I wanted to know if i could do it with a macro as the 3 cells i wish
to put together could be any where, i want to try and click on the
first of the 3 cells, press the button and them the cells join
together on another part of the spreadsheet,