How To CONCATENATE In Excel, VBA, & Google Sheets

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

Concatinate Main

CONCATENATE Function Overview

The CONCATENATE Function Combines text strings together.

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

concatenate formula syntax

(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)

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 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)

Concatinate EX two

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.

Concatinate EX THREE

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)

Concatinate EX Four

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

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.

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.

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

Excel Practice Worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!