[lwptoc]
Summary
The Excel CONCATENATE function concatenates (joins) join up to 30 values together and returns the result as text. In Excel 2019 and later, the CONCAT and TEXTJOIN functions are better, more flexible alternatives.
Use CONCATENATE, one of the text functions, to join two or more text strings into one string.
For example,
=CONCATENATE("Stream population for ", A2, " ", A3, " is ", A4, "/mile.")
=CONCATENATE(B2, " ",C2)
Important: In Excel 2016, Excel Mobile, and Excel for the web, this function has been replaced with the CONCAT function. Although the CONCATENATE function is still available for backward compatibility, you should consider using CONCAT from now on. This is because CONCATENATE may not be available in future versions of Excel.
Purpose
Use CONCATENATE, Join text together
Return value
Text joined together.
Syntax
=CONCATENATE (text1, text2, [text3], ...)
Arguments
text1 – The first text value to join together.
text2 – The second text value to join together.
text3 – [optional] The third text value to join together.
Usage notes
The CONCATENATE function concatenates (joins) join up to 30 values together and returns the result as text. In Excel 2019 and later, the CONCAT function and TEXTJOIN function are better, more flexible alternatives.
The CONCATENATE function accepts multiple arguments called text1, text2, text3, etc. up to 30 total. Values may be supplied as cell references, and hard-coded text strings. Only the first argument is required, and values are concatenated in the order they appear. For example, to concatenate the value of A1 and B1, separated by a space, you can use CONCATENATE like this:
=CONCATENATE(A1," ",B1)
The result of this formula is the same as using the concatenation operator (&) manually like this:
=A1&" "&B1 // manual concatenation
The ampersand character (&) is an alternative to CONCATENATE. The result is the same, but the ampersand is more flexible, and creates formulas that are shorter and (arguably) easier to read.
Number formatting
When concatenating numeric values like dates, times, percentages, etc., number formatting will be lost. For example, with the date 1-Jul-2021 in cell A1, the date reverts to a serial number during concatenation:
=CONCATENATE("Date: ",A1) // returns "Date: 44378"
To apply formatting during concatenation use the TEXT function :
=CONCATENATE("The date is ",TEXT(A1,"mmmm d")) // "Date: July 1"
The CONCATENATE function will not handle ranges:
=CONCATENATE(A1:D1) // does not work
To concatenate values in ranges, see the CONCAT function. To concatenate many values with a common delimiter, see the TEXTJOIN function. TEXTJOIN can do everything CONCAT can do, but can also accept a delimiter and optionally ignore empty values.
Examples
To use these examples in Excel, copy the data in the table below, and paste it in cell A1 of a new worksheet.
| Data | ||
| brook trout | Andreas | Hauser |
| species | Fourth | Pine |
| 32 | ||
| Formula | Description | |
| =CONCATENATE(“Stream population for “, A2, ” “, A3, ” is “, A4, “/mile.”) | Creates a sentence by joining the data in column A with other text. The result is Stream population for brook trout species is 32/mile. | |
| =CONCATENATE(B2, ” “, C2) | Joins three things: the string in cell B2, a space character, and the value in cell C2. The result is Andreas Hauser. | |
| =CONCATENATE(C2, “, “, B2) | Joins three things: the string in cell C2, a string with a comma and a space character, and the value in cell B2. The result is Andreas, Hauser. | |
| =CONCATENATE(B3, ” & “, C3) | Joins three things: the string in cell B3, a string consisting of a space with ampersand and another space, and the value in cell C3. The result is Fourth & Pine. | |
| =B3 & ” & ” & C3 | Joins the same items as the previous example, but by using the ampersand (&) calculation operator instead of the CONCATENATE function. The result is Fourth & Pine. |
Common Problems
| Problem | Description |
| Quotation marks appear in result string. | Use commas to separate adjoining text items. For example: Excel will display =CONCATENATE(“Hello “”World”) as Hello”World with an extra quote mark because a comma between the text arguments was omitted. Numbers don’t need to have quotation marks. |
| Words are jumbled together. | Without designated spaces between separate text entries, the text entries will run together. Add extra spaces as part of the CONCATENATE formula. There are two ways to do this:
|
| The #NAME? error appears instead of the expected result. | #NAME? usually means there are quotation marks missing from a Text argument. |
Best practices
| Do this | Description |
| Use the ampersand & character instead of the CONCATENATE function. | The ampersand (&) calculation operator lets you join text items without having to use a function. For example, =A1 & B1 returns the same value as =CONCATENATE(A1,B1). In many cases, using the ampersand operator is quicker and simpler than using CONCATENATE to create strings. Learn more about using operation calculators. |
| Use the TEXT function to combine and format strings. | The TEXT function converts a numeric value to text and combines numbers with text or symbols. For example, if cell A1 contains the number 23.5, you can use the following formula to format the number as a dollar amount: =TEXT(A1,”$0.00″) Result: $23.50 |
Download Template
Click here to download the sample Excel file of CONCATENATE Function
Notes
CONCATENATE can join up to 30 text items together.
Text items can be text strings, numbers, or cell references that refer to one cell.
Numbers are converted to text when joined. If you need to specify a number format for a number being joined, see the TEXT function.
The ampersand character (&) is an alternative to CONCATENATE. The result is the same, but the ampersand is more flexible, and creates formulas that are shorter and (arguably) easier to read.
