Concatenating a range of values in Excel

Question

I have a set of values that I would like to concat together using a formula. At the moment I am doing it manually using the following formula:

=CONCATENATE(A1,":",A2,":")

Is there a way to set a range using the CONCATENATE function?

Answer 1

In a word - "No"

Excel has no built-in worksheet function that concatenates a range - MCONCAT function from MOREFUNC add-in could do that but I'm not sure the add-in is available any more, otherwise you need VBA or a UDF.....or continue with your workaround

Answer 2

You can try this way explained in the image below.

In the formula (do not copy it identically because of my territorial version) you can write the formula once and copy it down until you need.