hi, i hv some data points in the first 5 columns of a sheet. i concatenated
them to the corresponding cell of the 6th column. now the data in every
column has to be in a separate line in the cells of the 6th column. i used
alt+enter for the first cell and tried using a brush to format the other
cells too but it doesnt work. can u plz help as i wud not b able to format
each cell one by one since i have 40000 rows of data....
thanks...

Think you should be able to (re)do it quickly like this,

Assume source data to be concatenated is in cols A to E, from row2 down, and
the concat result is to display in col F

Place something like this** in F2:
=A2&CHAR(10)&B2&CHAR(10)&C2&CHAR(10)&D2&CHAR(10)&E 2

Format F2 to wrap text, via Format Cells Alignment tab Check "Wrap
text" OK. Copy F2 down all the way.

The CHAR(10) is the wrap character (== pressing Alt+ENTER)

**adapt/extend the concat formula to suit, depending on whether you need
commas, space-commas, etc as separators

If A2:E2 contains the numbers say: 1,2,3,4,5
then F2 will appear as:

1
2
3
4
5
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Malik" wrote:
hi, i hv some data points in the first 5 columns of a sheet. i concatenated
them to the corresponding cell of the 6th column. now the data in every
column has to be in a separate line in the cells of the 6th column. i used
alt+enter for the first cell and tried using a brush to format the other
cells too but it doesnt work. can u plz help as i wud not b able to format
each cell one by one since i have 40000 rows of data....
thanks...

Thanks so much for ur response...very helpful indeed

"Max" wrote:

Think you should be able to (re)do it quickly like this,

Assume source data to be concatenated is in cols A to E, from row2 down, and
the concat result is to display in col F

Place something like this** in F2:
=A2&CHAR(10)&B2&CHAR(10)&C2&CHAR(10)&D2&CHAR(10)&E 2

Format F2 to wrap text, via Format Cells Alignment tab Check "Wrap
text" OK. Copy F2 down all the way.

The CHAR(10) is the wrap character (== pressing Alt+ENTER)

**adapt/extend the concat formula to suit, depending on whether you need
commas, space-commas, etc as separators

If A2:E2 contains the numbers say: 1,2,3,4,5
then F2 will appear as:

1
2
3
4
5
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Malik" wrote:
hi, i hv some data points in the first 5 columns of a sheet. i concatenated
them to the corresponding cell of the 6th column. now the data in every
column has to be in a separate line in the cells of the 6th column. i used
alt+enter for the first cell and tried using a brush to format the other
cells too but it doesnt work. can u plz help as i wud not b able to format
each cell one by one since i have 40000 rows of data....
thanks...

Welcome, Malik.

From where you're reading these responses, could you kindly click the "Yes"
button to the question: Was this post helpful to you? <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Malik" wrote:
Thanks so much for ur response...very helpful indeed