How to Use CONCATENATE/CONCAT in Excel

Combining data from multiple cells in Excel can enhance data organization and readability. One of the simplest ways to achieve this is by using the ampersand (&) operator.

Combining Cells Using the `&` Operator

The ampersand operator (&) in Excel allows you to join text strings, numbers, or cell values into a single string without the need for a function.

Syntax:

=cell1&cell2

To combine the values of cells A1 and B1, you can use the following formula:

=A1&B1

Enter this formula in the cell where you want the combined result.

Adding Separators Between Combined Values

To insert a space or any other separator between the combined cell values, include the separator enclosed in double quotation marks in your formula. For example, to add a space between the contents of A1 and B1:

=A1&" "&B1

This formula will join the values of A1 and B1 with a space between them.

You can also use other separators like commas, hyphens, or any character of your choice. For example, to separate the values with a comma:

=A1&","&B1

Combining Text Strings and Cell Values

You can include text strings in your formula by enclosing them in double quotation marks. To combine the content of A1, the word "and", and B1:

=A1&" and "&B1

This formula will produce a string that includes the values from A1 and B1 with "and" in between.

Combining Cells Using the CONCATENATE/CONCAT Function

Another method to merge cell values is by using the CONCATENATE or CONCAT function. These functions allow you to join two or more text strings or cell values into one.

From Excel 2016 onwards, the CONCATENATE function was replaced with CONCAT. However, you can still use CONCATENATE in older versions of Excel.

Syntax for CONCAT:

=CONCAT(text1, [text2], ...)

Syntax for CONCATENATE:

=CONCATENATE(text1, [text2], ...)

Here, text1, text2, etc., represent the items you want to join, which can be text strings, cell references, or a combination of both.

Combining Text Strings

To join text strings using the CONCAT function, enter the text strings as arguments, ensuring they are enclosed in double quotation marks:

=CONCAT("Hello", " ", "World!")

This formula will result in "Hello World!".

Combining Cell Values

To concatenate the values of cells A1 and B1 using CONCAT:

=CONCAT(A1, B1)

This will merge the contents of A1 and B1 without any separators.

Adding Separators with CONCAT

To include a space or other separators between cell values when using CONCAT, include the separator as an additional argument:

=CONCAT(A1, " ", B1)

This formula will join A1 and B1 with a space in between.

Including Special Characters

You can use special characters as separators. For example, to separate values with a comma:

=CONCAT(A1, ",", B1)

This will place a comma between the contents of A1 and B1.

Combining Text Strings and Cell Values

To mix text strings with cell values, include the text strings in double quotation marks within your formula. For example:

=CONCAT(A1, " and ", B1)

This will join the value of A1, the word "and", and the value of B1 into a single string.

Concatenating Columns

If you have data in separate columns and wish to combine them, you can apply a concatenation formula to the first row and then drag it down to apply to other rows. For example, to combine columns containing first and last names:

=A2&" "&B2

Drag the fill handle down to apply the formula to additional rows.

After applying the formula, you'll have a new column with the combined names.

Concatenating a Range of Cells

To combine a range of cells without any separators using the CONCAT function:

=CONCAT(A1:F1)

This will merge all values from A1 to F1 into one string.

To include separators between the values, you need to specify each cell and include the separator. For example, to add spaces:

=CONCAT(A2, " ", B2, " ", C2, " ", D2, " ", E2)

Using the TEXTJOIN Function

The TEXTJOIN function is another powerful way to combine a range of cells with a delimiter. It allows you to specify a separator and choose whether to ignore empty cells.