How To CONCATENATE In Excel & Google Sheets **Updated 2022**

This tutorial demonstrates how to use the CONCATENATE, CONCAT, and TEXTJOIN Functions in Excel and Google Sheets to merge strings of text together.

 

CONCATENATE and CONCAT Functions

Prior to Excel 2019, the only concatenate function available was the CONCATENATE Function. In recent years, Excel has introduced the CONCAT and TEXTJOIN Functions. First let’s cover the CONCATENATE AND CONCAT Functions.

These functions join together several strings of text into one.

=CONCATENATE(B3," ",C3)
=CONCAT(B3," ",C3)

As seen above, the two functions have the same inputs. What is the difference between them then?

CONCATENATE and CONCAT to Join Multiple Cells

The CONCAT Function allows you to easily merge together a range of cells, instead of entering the cells one by one.

=CONCATENATE(B3,C3,D3,E3)
=CONCAT(B3:E3)

The CONCATENATE Function requires you to input all the individual cells you wish to join.

Quick Way to Use CONCATENATE to Join Multiple Cells

If you don’t have access to CONCAT, there is a quicker way to use CONCATENATE: Instead of clicking the cells one by one with commas in between, you can press and hold CTRL key before clicking each cell. That helps you save the step of pressing a comma.

CONCATENATE and CONCAT with Number/Date

Both CONCATENATE and CONCAT are text functions. If you concatenate numbers together, they will be stored as text:

The same thing happens for dates.

=CONCATENATE(B3,"-",C3,"-",D3)
=CONCAT(B3,"-",C3,"-",D3)

To overcome the issues above, you can use the VALUE Function to convert the concatenated values back into numerical values.

=VALUE(CONCATENATE(B3,C3,D3))
=VALUE(CONCAT(B3,C3:D3))

Alternative to CONCATENATE and CONCAT

A very common way to join cells is to use the ampersand key (&). It can simulate the above scenarios. One example:

=B3&" "&C3

This is probably the easiest way to concatenate two cells.

TEXTJOIN Function

If you want to concatenate text together, separated by a delimiter (comma, semi-colon, space, line break, etc.) then use the TEXTJOIN Function. It is available in Excel 2016 and above.

=TEXTJOIN(" ",false,B3:C3)

The above combines First Name and Last Name (column B and C) with a space as the delimiter (the character that splits the word) and a choice to include blanks (next example will be more obvious).

CONCATENATE and CONCAT Function in Google Sheets

The CONCATENATE function and ampersand key work the same in Google Sheets.

The CONCAT function doesn’t quite work the same in Google Sheets. It allows joining a maximum of just two cells and doesn’t allow its argument as a range.