Wednesday, December 7, 2011

Excel - Concatenate range

Not as pretty as a custom function, but gets the job done.
To concatenate all values in range A2:A100, place the following formula into B2 and drag/fill it down to B100.
If you want to concatenate all values in a range on the condition that there's a 1 in column B:
To avoid the , or 0 at the beginning, in the first cell that should have a value, enter the value manually and copy the formula to the cell below it.


  1. Thank you for posting this - it's exactly what I was searching for.

  2. Thanks - much easier than dealing with VBA. Works perfectly :)

  3. The beauty of simplicity - THANKS!