using range in CONCATENATE function

Question

I would like to combine the content of many cells in one cell. One possible solution for me is CONCATENATE function.

If I use this:

=CONCATENATE(B1,C1,D1)

it works fine, but for:

=CONCATENATE(B1:D1)

I am seeing such a value: #VALUE in the cell. How can I define range in a CONCATENATE function?

Answer 1

Unfortunately the Excel CONCATENATE function doesn't work for a range or array (it does in Google Sheets).

There is an example here of a simple UDF that does work with arrays in Excel.