Return to List of Excel Functions

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

Download Example Workbook

Download the example workbook

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

Concatinate Main

 

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)

Concatinate EX one

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)

Concatinate EX two

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:

Concatinate EX THREE

The same thing happens for dates.

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

Concatinate EX Four

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

Concatinate EX FIVE

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

Concatinate EX SIX

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

Concatinate EX Seven

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)

Textjoin-01

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.

Concatinate Google

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.