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!

 

[pgc01]

Hi

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

To concatenate a range you can use the function TEXTJOIN() available since excel 2016.

If you have an older version you can use vba and, for ex., write a udf.

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? :wink:

For ex., you evaluate the cell formula and then use INDEX().

Also in this case you can use vba and write a udf, or use a name to evaluate the cell.

 

[XLearner]

To concatenate a range you can use the function TEXTJOIN() available since excel 2016.

CONCAT (Excel >/=2013) also works. =CONCAT(A1:A3)

 

[ww4612]

try this function

Function j(ParamArray t())
dim i as integer
for i = lbound(t) to ubound(t)
j=j & t(i)
next i
end function

=j(text1,text2,text3)

 

[Aladin Akyurek]

CONCAT (Excel >/=2013) also works. =CONCAT(A1:A3)

The Fx on my 2013 copy doesn't list CONCAT, while CONCATENATE and & are available.

 

[pgc01]

CONCAT (Excel >/=2013) also works. =CONCAT(A1:A3)

Hi. Thanks, I believe that it's only for xl2016+. Can you confirm?

 

[XLearner]

@Aladin & pgc01. You're right (CONCAT is avail. in >/= XL 2016). Sorry about that and thanks for pointing this out

 

[IlyaK]

Hi! Thanks everyone for replies!

@pgc01: Yes, indeed, I could use VBA and named ranges, but possibility of getting elements from array stored in cell would be great, isn't it? Also if i will use udf, it could be mach more slowly than regular formula (even CSE) or..?

@Aladin: unfortunately I have 2013 version. I have read description of concat() function and haven't seen example that show possibility of use range (more than 1 cell) as argument, is it realy could do that unlike concantination()?

 

[Aladin Akyurek]

Try to search for ACONCAT...

Hi! Thanks everyone for replies!

@pgc01: Yes, indeed, I could use VBA and named ranges, but possibility of getting elements from array stored in cell would be great, isn't it? Also if i will use udf, it could be mach more slowly than regular formula (even CSE) or..?

@Aladin: unfortunately I have 2013 version. I have read description of concat() function and haven't seen example that show possibility of use range (more than 1 cell) as argument, is it realy could do that unlike concantination()?

 

[pgc01]

... but possibility of getting elements from array stored in cell would be great, isn't it?

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.