2 question: Concatination of Array and access to arrays element in cell

[IlyaK]

Hi everyone!

Here is a task about array, I can't resolve.

I have the array like {text1,text2,text3} and I want to joint elements of the array in string like "text1text2text3"
I try to use array formulas, but it doesn' work Why Mcrosoft haven't realize sum-like formula for text

And one more question about arrays: Is any way to get element of array from cell with formula like above:
"={text1,text2,text3}"? If there isn't, may be some MVP-s could offer to realize that function to MS?

Many thanks for replies!

 

[IlyaK]

Perhaps you're right. For all reasons you mentioned it's best realization! But! Array formulas isn't easy to use thing, isn't it? And if MS create the possibility to store array in single cell-why there isn't a way to extract it?

 

[pgc01]

And if MS create the possibility to store array in single cell-why there isn't a way to extract it?

Well, there is, you just have to use vba or a name.

This means, of course, that MS could decide to implement sometime in the future a native function to do it

Concat() and TextJoin() appeared in xl2016 after many years of demand by the users, that could not understand why there was no function to join the values in a range.

Maybe you can start a movement for your function?

 

[IlyaK]

This is a good idea! Like those petitions on change.org?
In realy tasks I haven't really need in this realisation. It just would be useful (IMHO) for huge_array_formula debugging,
because it allow to split such formulas in some cells.
Anyway thanks for interesting thread!

 

[IlyaK]

Well, I must say i don't agree.

I think that 1 cell - 1 simple (scalar) value is a great design decision.

You say you want to store array elements.

The worksheet structure, a rectangular grid, is perfect to store array elements.

If you want to store 1D array elements you can use a vector, a group of contiguous cells either in the same column or in the same row.
If you want to store 2D array elements I can use a rectangular range of cells.

So, I really don't think that I need a cell to be able to store an array.
I use the worksheet row-column grid and store the elements of an array in a way that makes it simple to read, search, substitute, insert, delete, ...

We can, of course, agree to disagree.

It's fancy to read this tread in 2023