Concatenation in Excel?

Question

In Excel, instead of doing =A1 + A2 + ... + A10, you can do =SUM(A1:A10).

But is there an analogous formula for concatenating? i.e. a shorter way to do =A1 & A2 & ... &A10?

Answer 1

You need an Add-in to use the MCONCAT function. You will still have character limitations in the cell, though.

The Add-in is available here:
http://xcell05.free.fr/

Answer 2

I agree with Robert. I looked through the different available Text functions and there really isn't a combination that can make it shorter. The only way you could make it shorter and easier would be if you created a custom function using VBA. Then you could save the custom function in your Personal workbook or make it an add-in so that the function is always available. You could make it so that the function looks like

=JOIN()

I'm pretty sure there isn't already a JOIN function as a standard function in Excel.

From what I've been learning about Excel and VBA it looks as though the Concatenation function was designed for several individual text entries instead of for ranges. There's probably some good reasoning behind Microsoft doing that since an Excel cell cannot take over somewhere around 252 characters. By being able to specify ranges you could easily go over that quantity.

Answer 3

Unfortunately without VBA code there is not a quicker way. You can use =Concatenate(A1,A2,A3), but that isn't really any quicker.