Excel Hack - Concatenating (Adding Together) Text in Different Cells

Sometimes in Excel you have values (generally text) in different cells that you want to piece together into a single cell.  In the example below I have a set of fruits and tasty things I want to join together into a single cell:

Lovely old Excel gives us two ways to do this.  The first way, and the one I always remember, is the "&" operator.  In the example below I've already put the formula in cell C1 and am showing the formula structure in cell C2:

The formula simply means "takes what's in cell A2, add a space to it, then add what's in cell B2".

The second way is the "CONCAT" formula.  Here's an example:

Here the CONCAT formula takes a set of cell references and actual strings and joins them all together into a single result.

For (Geek Dad style) fun I can first transpose the column B values to a row (using this method) and then use mixed references (explained here) to create a crazy set of food ideas:

The mixed references in the formula here are used to keep to column A and row 1 as you copy the formula to other cells.  Specifically:

• $A2 - Keep to column A as you copy across columns, allow the row to increment as you copy down.
• B$1 - Allow the column to increment as you copy across, keep the row as 1 as you copy down.

Resulting in:

Comment below if you've used CONCAT or &.