MS Excel: How to use the CONCATENATE Function (WS)

This Excel tutorial explains how to use the Excel CONCATENATE function with syntax and examples.

Description

The Microsoft Excel CONCATENATE function allows you to join 2 or more strings together.

The CONCATENATE function is a built-in function in Excel that is categorized as a String/Text Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the CONCATENATE function can be entered as part of a formula in a cell of a worksheet.

If you want to follow along with this tutorial, download the example spreadsheet.

Download Example

Syntax

The syntax for the CONCATENATE function in Microsoft Excel is:

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

Parameters or Arguments

text1, text2, ... text_n

The strings that you wish to join together. There can be up to 255 strings that are joined together, up to a maximum of 8,192 characters.

Returns

The CONCATENATE function returns a string/text value.

Applies To

• Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function

• Worksheet function (WS)

Example (as Worksheet Function)

Let's look at some Excel CONCATENATE function examples and explore how to use the CONCATENATE function as a worksheet function in Microsoft Excel:

Based on the Excel spreadsheet above, the following CONCATENATE examples would return:

=CONCATENATE(A2,B2,C2,D2)
Result: "EFGH"

=CONCATENATE(A3,B3,C3,D3)
Result: "5678"

=CONCATENATE(A4,B4,C4,D4)
Result: "abc123"

=CONCATENATE(A5,B5,C5,D5)
Result: "TechOnTheNet.com"

=CONCATENATE(A5," is great")
Result: "TechOnTheNet is great"

=CONCATENATE(A3,"+",B3," equals ",11)
Result: "5+6 equals 11"

Concatenate Space Characters

When you are concatenating values together, you might want to add space characters to separate your concatenated values. Otherwise, you might get a long string with the concatenated values running together. This makes it very difficult to read the results.

Let's look at an easy example.

Based on the Excel spreadsheet above, we can concatenate a space character within the CONCATENATE function as follows:

=CONCATENATE(A2," ",B2)
Result: "DigMinecraft.com website"

In this example, we have used the second parameter within the CONCATENATE function to add a space character between the values in cell A2 and cell B2. This will prevent our values from being squished together.

Instead our result would appear as follows:

"DigMinecraft.com website"

Here, we have concatenated the values from the two cells (A2 and B2), separated by a space character.

Concatenate Quotation Marks

Since the parameters within the CONCATENATE function are separated by quotation marks when they are string values, it isn't straight forward how to add a quotation mark character within the result of the CONCATENATE function.

Let's start with a fairly easy example that shows how to add a quotation mark to the resulting string using the CONCATENATE function.

Based on the Excel spreadsheet above, we can concatenate a quotation mark as follows:

=CONCATENATE(A2,"""",B2)
Result: 2" is short for 2 inches

In this example, we have used the second parameter within the CONCATENATE function to add a quotation mark into the middle of the resulting string.

Since our parameters are enclosed in quotation marks, we use 2 additional quotation marks within the surrounding quotation marks to represent a quotation mark in our result as follows:

""""

Then when you put the whole function call together:

=CONCATENATE(A2,"""",B2)

You will get the following result:

2" is short for 2 inches

Frequently Asked Questions

Question:For an IF statement in Excel, I want to combine text and a value.

For example, I want to put an equation for work hours and pay. If I am paid more than I should be, I want it to read how many hours I owe my boss. But if I work more than I am paid for, I want it to read what my boss owes me (hours*Pay per Hour).

I tried the following:

=IF(A2<0,"I owe boss" ABS(A2) "Hours","Boss owes me" ABS(A2)*15 "dollars")

Is it possible or do I have to do it in 2 separate cells? (one for text and one for the value)

Answer: There are two ways that you can concatenate text and values. The first is by using the & character to concatenate:

=IF(A2<0,"I owe boss " & ABS(A2) & " Hours","Boss owes me " & ABS(A2)*15 &  " dollars")

Or the second method is to use the CONCATENATE function:

=IF(A2<0,CONCATENATE("I owe boss ", ABS(A2)," Hours"), CONCATENATE("Boss owes me ", ABS(A2)*15,  " dollars"))