formula to gather non-empty cell info

[J_J]

Hi,
I have 20 rows of cells which some have text info and some empty.
I need a formula to use to add-up these text info separeted with commas
excluding the blank cells.
Say A1 contains "this", E1 contains "is", and I1 contains "it!". All the
remaining cells till AA1 are blank (but can contain info).
I need to use a formula in cell AA1 that will gather non empty cells info in
row1 and display "this, is, it".
The same story applies for row2, row3, ...etc.
What should be the formula in AA1:AA20
Thanks
J_J

 

[Dodo]

What should be the formula in AA1:AA20

Something like this in AA1:

=IF(A1="";"";A1&", ")&IF(B1="";"";B1&", ")&IF(C1="";"";C1&", ")&IF(D1
="";"";D1&", ")&IF(E1="";"";E1&", ")& etc. . . . . . . .

Copy this file down.

There are probably smarter ways to do this (matrix formula, or custom
function in VBA) but I haven't thought of a working one yet.

 

[Dodo]

Something like this in AA1:

=IF(A1="";"";A1&", ")&IF(B1="";"";B1&", ")&IF(C1="";"";C1&", ")&IF(D1
="";"";D1&", ")&IF(E1="";"";E1&", ")& etc. . . . . . . .

Copy this file down.

There are probably smarter ways to do this (matrix formula, or custom
function in VBA) but I haven't thought of a working one yet.

Copy formula down I should have written.

 

[J_J]

Thank you Dodo,
But is there not a restriction with the number of IF's we can use in one
statement?
And surely the number I'll need is greater then this number.
Regards
J_J

 

[Dodo]

Thank you Dodo,
But is there not a restriction with the number of IF's we can use in one
statement?

Left of AA is 26 columns. With nested functions (limit is 7) that would be
a problem, but by doing it a different way the only limit you are running
into is the number of characters you can put in a formula in a cell: 1024.
For 26 colums you're not even halfway. ;-)))

http://office.microsoft.com/en-us/assistance/HP051992911033.aspx

I also found a reference to a custom function:

http://www.mcgimpsey.com/excel/udfs/multicat.html

I have not tried it but you probably can use it as a basis and add
something to test for cell content and when null, drop the comma/space.

 

[J_J]

Thanks a lot Dodo,
Much appreciated.
J_J

Left of AA is 26 columns. With nested functions (limit is 7) that would be
a problem, but by doing it a different way the only limit you are running
into is the number of characters you can put in a formula in a cell: 1024.
For 26 colums you're not even halfway. ;-)))

http://office.microsoft.com/en-us/assistance/HP051992911033.aspx

I also found a reference to a custom function:

http://www.mcgimpsey.com/excel/udfs/multicat.html

I have not tried it but you probably can use it as a basis and add
something to test for cell content and when null, drop the comma/space.