Since Excel has announced CONCATENATE function as the compatibility function, it might not be accessible in the later Excel versions. However, Excel has released a new function named “CONCAT” as a substitute for the CONCATENATE function.
So, from now on, Excel recommends you use the CONCAT function to join values from different cell ranges. In this article, we have compiled everything you must know about the CONCAT function.
Arguments for CONCAT Function
Excel’s CONCAT function returns the joined text strings from the number of cells ranges you pass down in a formula.
Syntax: CONCAT(text1, [text2],...) CONCAT function takes text1, text2, text3, etc up to 253 text arguments at maximum. When you pass down an argument in the CONCAT function, you must know when to use quotation marks.
Let’s say you entered three words (The, Great, Gatsby) to join directly in the formula. During such cases, you must enclose the words inside double quotation marks. Your formula should be =CONCAT(“The”, “Great”, “Gatsby”). Then, you’ll get TheGreatGatsby as a result. If you enter =CONCAT(The, Great, Gatsby), the formula will return #NAME? Error.
However, if you are referring to a cell value (A1, A2, A3), you can use the formula as =CONCAT(A1,A2,A3). It’ll return TheGreatGatsby. In this case, do not use quotation marks for cell references. Else, the formula will give you A1A2A3 value.
Things You Should Know Before Using CONCAT Function
- You can use the CONCAT function only on later Microsoft Offices like 2019 or 365 subscriptions.
- The CONCAT function supports characters up to 32767 at maximum. If you surpass the limit, you’ll encounter #VALUE! Error.
- Use double quotation marks (“”) for text strings and delimiters in the formula.
Examples of Using CONCAT Function
Join Texts Without Delimiter
If you need to join texts without delimiter or spaces, you can simply enter the cell references in the CONCAT function. For Instance, we have Title in Column C and Name in Column D. Now, to combine the Title and Name, enter the formula as
=CONCAT(C2, D2)
or,
=CONCAT(C2:D2)
In the formula, CONCAT functions take C2 as text 1 and D2 cell as text2. The formula merges the text strings of these two cells and returns Mrs.Braun.
After this, select the cell with the result and hover over the bottom-right corner. Once you see a small Plus sign cursor, drag it down to fill in the rest of the data. In short, Excel names this process as Fash-fill.
Join Texts With a Delimiter
When you pass down only the cell references in the CONCAT function, it just combines two values together as it is. Meaning, there won’t be any spaces unless either of the values has one.
But, you may often find the need to add a delimiter in between them. For Instance, a space in the name, dashes in words, / in per texts, and many more. For this, you can specify the delimiters while using the CONCAT function. You must enclose the delimiters inside the double quotation marks in the formula.
Check out these examples.
| Delimiter | Text 1 | Text 2 | Text 3 | Formula | Description |
| Space | Elizabeth | Smith | Brown | =CONCAT(A5, ” “, B5, ” “, C5) Elizabeth Smith Brown | In the formula, we have passed down A5 as text 1, “ ”, as text 2, B5 as text 3, and so on. Here, “ ” will add a space between text A5, B5, and C5. |
| / | 58 | Cost | Unit | =CONCAT(A2, ” “, B2, “/”, C2) 58 cost/unit | In this formula, we have passed down A2 as text 1, “ ”, as text 2, B2 as text 3, “/” as text 4 and so on. Here, “ ” will add a space in between the texts. Similarly, “/” will add a / delimiter between B2 and C2. |
| & | The | Sun | Moon | =CONCAT(A3, ” “, B3, ” “, “&”, ” “, C3) The Sun & Moon | Here, we have passed down both “ ” (space) and “&” arguments in the formula. The formula joins the value of A3, B3, and C3 with space and & delimiter in between. |
| – | China | Chinese | Mandarin Chinese | =CONCAT(A4, “-“, B4, “-“,C4) China-Chinese-Mandarin Chinese | In this formula, we have passed down the dash delimiter “-”. So, the formula will combine the texts of A4, B4, C4 with dashes in between. |
How to Nest CONCAT With Other Functions in Excel?
TRIM and CONCAT
As I mentioned earlier, the CONCAT function returns the merged texts as the original values are in the cell references. If there are irregular spaces in text strings, your output will also have them. In such cases, you could nest the TRIM function with the CONCAT function.
=TRIM(CONCAT(text1, [text2]))Let’s take an example that you have First Names in Column A and Last Names in Column B. To remove spaces and join texts to form a full name, you can use this formula.
=TRIM(CONCAT(A4," ",B4))
Here, the CONCAT function returns the concated value of A4 and B4 which is “ Olympia Nutley.” Then, TRIM function eliminates unnecessary spaces. Thus, you’ll have only “Olympia Nutley” as a final result.
PROPER and CONCAT
There can be situations where the texts you want to join are in lowercase. If you wish to capitalize the first letters of the words, you can nest PROPER and CONCAT functions together. Here’s how you do it.
=PROPER(CONCAT(text1, [text2]))In the given example, the first name and last name are in lowercase. To capitalize while combining texts, your formula would be
=PROPER(CONCAT(A10," ",B10))
The formula returns “Mortimer Dankov” as an output.
How to Avoid Errors While Using CONCAT Function?
While using the CONCAT function, it is most likely that you either encounter #NAME? or #VALUE! Error. To avoid Value error, make sure you do not exceed the character limit in a formula that is 32,767. Similarly, to resolve or prevent #NAME? Error, you must place text strings inside the double quotation marks.