CONCATENATE versus TEXTJOIN with array formula (CSE)

[zylstrar]

I would like to get the same result with TEXTJOIN (as an array formula in one cell) as I get with CONCATENATE. For example:
I want these results:

But I get this:

 

[BSALV]

Map2
	B	C	D	E
2		red	blue	yellow
3	roses	red roses	blue roses	yellow roses
4	violets	red violets	blue violets	yellow violets
5	daisies	red daisies	blue daisies	yellow daisies
Blad4

Cell Formulas
Range	Formula
C3:E5	C3	=TEXTJOIN(" ",1,C$2,$B3)

 

[mikerickson]

"I would like to get the same result with TEXTJOIN (as an array formula in one cell) as I get with CONCATENATE."

Why?
CONCATENATE and TEXTJOIN are two different functions. The reason there are two functions is so that we can get results with one that you can't with the other and vice versa.
If CONCATENATE works for you, use it.

 

[zylstrar]

Why?
If CONCATENATE works for you, use it.

Yes, I was really thinking the same thing, but "CONCATENATE may not be available in future versions of Excel."
So if it isn't available (in the future), then what? CONCAT is also natively array-based, and so works like TEXTJOIN, even though it is supposed to be the replacement for CONCATENATE. : /

@BSALV, this is actually what I used. The only thing is that with a CSE formula I only have to change one formula to adjust all cells, whereas with a regular formula I must remember to select all cells first then Ctrl + Enter to adjust all. I thought there was another way of entering a formula once to apply to a range, but perhaps I am mistaken.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

What about just doing it like this?
(Further to the first point above, if you have MS 365, you do not need the CSE formula confirmation.)

22 02 10.xlsm
	A	B	C	D
1		red	blue	yellow
2	roses	red roses	blue roses	yellow roses
3	violets	red violets	blue violets	yellow violets
4	daisies	red daisies	blue daisies	yellow daisies
Sheet2 (3)

Cell Formulas
Range	Formula
B2:D4	B2	=B1:D1&" "&A2:A4
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[zylstrar]

Wow, sometimes my brain doesn't work. Either that or I'm too interested in doing things the most difficult way. : /

Now I understand why Microsoft is phasing out CONCATENATE.

I love this answer to the question, "Why use CONCATENATE in Excel?": Why use CONCATENATE vs & in Excel?.

(I entered my version of OS and Excel in my account details.)

 

[Peter_SSs]

I entered my version of OS and Excel in my account details.

Thanks for doing that.

So, as I mentioned before, you don't need CSE entry for the formula that I suggested - or for the original formula you were using with Concatenate()

 

[zylstrar]

you don't need CSE entry for the formula that I suggested

Good point. That method can be used very generally. It is probably what I was thinking of when I was remembering the existence of another way.