In this example let’s concatenate column 1 and have the list separated by commas.

Using the CONCATENATE function we could use the formula: Using the & we could use the formula: Both not very practical as the range gets bigger. A better solution is to create a user defined function in VBA. Something like this will do the trick. With this VBA code, the formula needed becomes this. Much more simple.

How To Concatenate A Range With A Delimiter - 5