As Seen on TechCrunch: Finofo Raises $1.65M CAD!

Excel Guide

How to Concatenate in Excel: A Step-by-Step Guide

If you work with data in Microsoft Excel, you may find yourself in need of a way to combine two or more cells into one. This process is known as concatenation, and Excel provides a couple of different ways to accomplish it. In this article, we'll take a look at how to concatenate in Excel using the CONCAT function and the & (ampersand) operator.

Using the CONCAT Function

The CONCAT function was introduced in Excel 2016 and is the recommended way to concatenate in Excel. The function takes two or more arguments, which can be individual cells or ranges of cells. For example, to concatenate cells A1 and B1, you would use the following formula:

=CONCAT(A1, B1)

You can also concatenate cells from different sheets:

=CONCAT(Sheet1!A1, Sheet2!B1)

If you want to concatenate a range of cells, use a cell reference for the first cell in the range, followed by a colon (:), and then a cell reference for the last cell in the range:

=CONCAT(A:A)

Using the & (Ampersand) Operator

The & (ampersand) operator is another way to concatenate in Excel. The & operator takes two arguments and concatenates them into a single value. For example:

=A1 & B1

You can also use the & operator to concatenate cells from different sheets:

=Sheet1!A1 & Sheet2!B1

To concatenate a range of cells:

=A:A

Concatenating Text and Numbers

Keep in mind that Excel treats text and numbers differently when concatenating. If you concatenate the text "12" and the number 3, you will get the text "123". To force Excel to treat a number as text, you can use the TEXT function:

=CONCAT(TEXT(12, "0"), 3)

Concatenating Cell Values with Line Breaks

Excel treats line breaks differently than other characters. To avoid issues, use the SUBSTITUTE function to replace line breaks with spaces:

=CONCAT(A1, " ", SUBSTITUTE(B1, CHAR(10), " "))

Concatenating Cell Values with a Delimiter

To concatenate cells with a delimiter, use the JOIN function. For example, to concatenate cells in column A with a comma as the delimiter:

=JOIN(",", A:A)

Concatenating Non-Contiguous Cells or Ranges

For non-contiguous cells or ranges, use the CONCAT function or the & operator with each cell or range address:

=CONCAT(A1, C1, E1)
=A1 & C1 & E1

Concatenating Cells Based on Conditions

Use the CONCAT function with the IF function to concatenate cells based on conditions:

=CONCAT(IF(A1="John", A1 & ", " & B1, ""))

For multiple conditions:

=CONCAT(IF(OR(A1="John", A1="Jane"), A1 & ", " & B1, ""))

By using these methods, you can efficiently concatenate cells in Excel based on your specific requirements.