display contents of array in cell

Originally Posted by each

Does anyone know if I can display the contents of an array in a cell.

eg in simplest form the array formula in cell b1 is {=a1:a7)
the contents of these 7 cells are numbers 1 to 7

therefore array is {1;2;3;4;5;6;7)

How can i refer to cell b1 in another cell and get that cell to display the array as 1;2;3;4;5;6;7 (or preferably 1,2,3,4,5,6,7)

I need a formula as a solution (no help doing f2 then f9) and not a macro.

Can anyone help, is it poss?

as

=A1&", "&A2&", "&A3&", "&A4&", "&A5&", "&A6&", "&A7

or you can test some elements as

=A1&", "&A2&", "&A3&", "&A4&", "&A5&IF(A6<>"",", "&A6,"")&IF(A7<>"",", "&A7,"")

hth
---

Originally Posted by each

Thanks for your help but this is not what I am looking for.

The example I gave was a simplified version of the actual problem. In reality the data only exists in the array and is not neatly presented on the spreadsheet already.

I need a function that will refer to the single array cell and pull out each of the values from the array - and display those values in a single cell.

Your original request was for a formula, but this is now a Function.

In the VB editor, (Alt-F11) select a Module (or Insert, Module) and copy

Please Login or Register  to view this content.

In the required cell put =Concat(A1:A7)

hth
---