Tom’s Tutorials For Excel: Couple of Concatenation Conundrums

Tom’s Tutorials For Excel: Couple of Concatenation Conundrums

Here are two examples of concatenating cells for whacky but not unheard-of situations.

The first example is alternating cells in column being concatenated in successive cells.
The formula in cell B4 and copied down to cell B21 is:
=OFFSET(A1,ROW()-1,)&IF(ISBLANK(OFFSET(A1,ROW()-1,)),""," ")
&OFFSET(A2,ROW()-1,)
Note that I broke the single-line formula into two lines here, for browser readability.



The second example is a running concatenation of successive cells into one single cell.
The formula in cell B5 and copied down to cell B13 is
=TRIM(B4&" "&A5&",").