In a Nutshell

To CONCATENATE is to “Join” or “Combine” data together.

Assume you have numerous columns of data to combine, and manually combining the data isn’t a possibility. You can use the CONCATENATE function to combine content from several cells into a single cell.

Syntax:

=CONCATENATE(text1, [text2], …)

text1 , text2, … are 1 to 255 strings of text, to be joined into a single text string

They can be text, numbers or single-cell references

Notes:

The CONCATENATE function is a worksheet function so it can be entered as part of a formula in a worksheet cell (see examples below).

Each argument listed in the brackets can either be a string of text, or array of strings, such as a range of cells.

CONCAT replaced the CONCATENATE function in Excel 2019, but the CONCATENATE function will stay available for compatibility with earlier versions of Excel.

Examples:

Let us have a look at the following example (Download the example workbook to follow along):

The CONCATENATE function below concatenates the text strings in cell B3 and cell C3, into one text string (in cell D3).

Remember the following when building a formula:

All Excel formulas must begin with an equal sign (=).
Commas are used as separators between the arguments (parameters) in any function.

So let’s combine the names in row 3:

=CONCATENATE(B3, C3)

The data has been combined, but with no delimiters. You will notice that Nico and Mellett are combined into one word, i.e., with no spaces in between.

Remember that Excel has no way of knowing if you want to add another character like a space or a dash, so you’ll have to manually add those characters as well.

We will now concatenate (or join) the first name, a space, and the last name. But we can’t just type a space on its own, we need to wrap it in double quotation marks to tell Excel that the space is a string of text that we want to concatenate.

Example: =CONCATENATE(A2, ” “, B2)

In the formula above, we will concatenate the contents of cell A2, a space character, and the contents of cell B2. These three arguments are all separated by commas.

Hover the mouse over the bottom right corner of the combined cell in D3, until the mouse cursor changes to a little black plus sign. Drag the cursor down to the bottom of the table, to apply the CONCATENATE formula to the entire Full Name column (col D):

Concatenating cells can also be done with an ampersand sign. The best part about using an ampersand is that there are no functions to learn.

Example: =B3 & C3

The formula above will combine the contents of cells B2 and C2.

But again, Excel does not know that we want a character in between. We need to add it in as a separate text string. Remember to wrap the space in double quotation marks to tell Excel this is a string of text:

For example: =B3 & ” ” & C3

There is virtually no difference between using the CONCATENATE function or the ampersand, so the one you end up using will depend on your own personal preference.

Concatenating using the CONCAT function

The CONCAT function was introduced in 2016, and it performs the same function as the CONCATENATE function, but you can select a range.

Example: =CONCAT(B3:F3)

This will join up all of the cells, from B3 through to F3:

How to Concatenate Dates and Times in Excel, using the TEXT function

Text, dates, and times can all be combined into a single sentence, in a single cell. But dates and times are stored as values, e.g. the date 24-Apr-2022 is stored as the number 44,675 and the time 9:05 AM is stored as 0.37847.

So if we concatenate as follows:

=CONCATENATE(B3,” “,C3,” “,D3)

…it won’t work very well, as the date and time values are no longer formatted properly:

“She will leave on 44675 0.378472222222222”

Instead, we need to tell Excel how to format the time and date, by using the TEXT function:

=TEXT(value, format_text)

value is the text string that we want to apply formatting to (this can be a cell reference, e.g. C3)

format_text is the formatting that we want to apply to that text string, e.g. “dd/mm/yyyy” or “hh:mm”

So, to fix the output in the example above, we need to apply formatting to the date and time values in C3 and D3, as follows:

=CONCATENATE(B3, ” “, TEXT(C3,”d mmmm yyyy”), ” at”, ” “, TEXT(D3,”hh:mm”))

And here is the result:

In case you missed it, here is a link to the example workbook.

Errors

CONCATENATE produces a #VALUE! error if the resulting string exceeds 32,767 characters.
A #NAME? error usually means there are quotation marks missing from a Text argument.

Excel versions supported

All versions

How to use the CONCATENATE function in Microsoft Excel