Description
The Microsoft Excel CONCAT function allows you to join 2 or more strings together. It was released in Excel 2016 and replaces the CONCATENATE function.
TIP: If you are running Excel 2013 or older, use the CONCATENTATE function instead.
If you want to follow along with this tutorial, download the example spreadsheet.
Syntax
The syntax for the CONCAT function in Microsoft Excel is:
CONCAT( 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.
Applies To
- Excel 2016
Type of Function
- Worksheet function (WS)
Example (as Worksheet Function)
Let's look at some Excel CONCAT function examples and explore how to use the CONCAT function as a worksheet function in Microsoft Excel:
Based on the Excel spreadsheet above, the following CONCAT examples would return:
=CONCAT(A2,B2,C2,D2)
Result: "ABCD"
=CONCAT(A3,B3,C3,D3)
Result: "1234"
=CONCAT(A4,B4,C4,D4)
Result: "TechOnTheNet.com"
=CONCAT(A5,B5,C5,D5)
Result: "alphabet"
=CONCAT(A5,"bet soup")
Result: "alphabet soup"
=CONCAT(A3,"+",B3," equals ",3)
Result: "1+2 equals 3"
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 CONCAT function as follows:
=CONCAT(A2," ",B2)
Result: "TechOnTheNet.com resource"
In this example, we have used the second parameter within the CONCAT 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:
"TechOnTheNet.com resource"
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 CONCAT 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 CONCAT function.
Here is an example that shows how to add a quotation mark to the start and end of the resulting string using the CONCAT function.
Based on the Excel spreadsheet above, we can concatenate a quotation mark to the front and the end as follows:
=CONCAT(,"""",A2," ",B2,"""")
Result: "TechOnTheNet.com is a great resource"
In this example, we have used the first parameter and fifth parameter within the CONCAT function to add a quotation mark to the start and end 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:
=CONCAT(,"""",A2," ",B2,"""")
You will get the following result:
"TechOnTheNet.com is a great resource"
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 CONCAT function:
=IF(A2<0,CONCAT("I owe boss ", ABS(A2)," Hours"), CONCAT("Boss owes me ", ABS(A2)*15, " dollars"))
