Join text from many cells

There are various functions in Excel that help you join the text (or numbers) in cells together to create a single cell.

To follow along go to 26.1

CONCATENATE and CONCAT

CONCATENATE has been in Excel for years. From the more recent versions it is replaced by CONCAT (but it will continue to work). It allows you to merge a number of cells together into one cell.

Below we want to turn column A, B and C into cells that look more like Column F. We can use CONCATENATE or CONCAT. As noted below all we need to do is point at the relevant cells and in this case separate them with a ‘-‘.

& as a shortcut

Instead of typing the full CONCAT formula you can use & as a shortcut. As per below you just

• point to the cell,
• type an &,
• then add a “-” (in this case) and
• add an & and so on

TEXTJOIN

From Excel 2016, you can replace CONCATENATE with TEXT JOIN.

The problem with CONCATENATE is that it is very manual. You need to click on each cell (you can’t point at a range of cells) and you need to manually add in any separators repeatedly.

TEXTJOIN addresses this. As per below, you tell it what

• the Delimiter is (- in this case)
• Whether to ignore empty cells or not (as you can now point at a range of cells this can be an issue)
• Where the cell/s with the text is. Note that you can point to individual cells or highlight a whole range of cells.