This tutorial demonstrates how to use the Excel CONCATENATE Function in Excel to merge strings of text together.

CONCATENATE Function Overview

The CONCATENATE Function Combines text strings together.

To use the CONCATENATE Excel Worksheet Function, select a cell and type:

(Notice how the formula inputs appear)

CONCATENATE function Syntax and inputs:

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

text1 – Strings of text, or an array of cells with strings of text.

text2 – The second text value to join together.

 

How to use the CONCATENATE and CONCAT Function in Excel:

The CONCAT function is available in Excel 2019 onwards and can replace the old CONCATENATE function. It joins several text strings 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 is much easier to use when you have a range of cells you want to join because you can simply input a range.

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

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

Quick Way to Use CONCATENATE to Join Multiple Cells

There is a quicker way to use CONCATENATE if you still don’t have CONCAT. 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

Do note that both CONCATENATE and CONCAT are text functions. Upon using either of them, the result is a text. For instance, you won’t be able to sum up these numbers in cell I3 and J3 after using either of them.

The same thing happens for dates as they are recognized as serial numbers and not text. You may not need to sum dates, but it doesn’t work well in filters and PivotTables.

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

To overcome the issues above, you can use VALUE to convert from text to 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

How does it differ from CONCATENATE or CONCAT? Functionally, none. It’s easier to type with ampersand if there are not many to join and looks neater. If there are many cells to join, CONCAT would be easier as you can input a range. Using the CTRL key with CONCATENATE might be faster as well.

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.

Additional Notes

Unfortunately, CONCATENATE will not accept an array of cells as an input. You must manually add each cell reference to the formula. <>

With the release of Excel 2016, Excel added the TEXTJOIN Function, a much better alternative to CONCATENATE. Our friends over at Spreadsheeto wrote a great article about using the new TEXTJOIN Function to combine strings of text.

Return to the List of all Functions in Excel