How to Use CONCATENATE Function

The CONCATENATE function is one of the text functions that allow you to join two or more cells together.

Formula:

= CONCATENATE (Cell 1, Cell 2…), comma is necessary

Explanations:

Cell 1 is required, the first part that you will need to join;

Cell 2 is required, when you need to join cell 1 with this cell;

… (Other cells) is optional, you can join as many as cells.

Cautions:

1. The CONCATENATE function will need a comma (,) between cells.

2. If TEXT content is included in the function, the quotation marks are necessary, while numbers do not need.

Examples 1: To join First Name, Last Name and Middle Name together

When using the CONCATENATE function directly links A2, B2, and C2 together, we get the result of "FirstLastMiddle".

=CONCATENATE(A2,B2,C2)

To format the results nicer, we can add spaces and comma in the function. If we want the result of "Last, First Middle", we can use the following combination:

=CONCATENATE(B2,","," ",A2," ",C2)

Example 2: to add 5 spaces in front of the cell content

To have spaces in front of cell contents, you can use the CONCATENATE function by adding spaces with quotation marks, the copy down to other cells.

=CONCATENATE(" ", B2)
There are 5 spaces in the quotation marks.

Notes:

1. Using the formula of the ampersand (&) can achieve the same results;

2. In Excel 2016, the CONCATENATE function is replaced with the CONCAT function. CONCATENATE function is still working In Excel 2016, but it may not be available in future versions.

Download CONCATENATE Function

Leave a Reply