CONCAT Function in Excel
Combine text in cells
Combine text in cells
The CONCAT Function in Excel is a text function that is used for combining text in cells. The function was introduced in MS Excel 2016 as a replacement to CONCATENATE function. However, you will find that both functions are available in MS Excel for backward compatibility.
It is a built-in function that can be used as a worksheet function in Excel. Let’s take an example. You need to combine/concatenate the strings below:
So here, we will use the formula below:
Using the formula above, we will get the following result:
You will notice that the above result is not acceptable as there is no space in between the characters. CONCAT function combines the strings as we instruct it to do. What we got is a long string with the concatenated values running together. Let’s modify this formula to add a space. To do so, we need to add another argument “ ” (two double quotes around a space).
Doing so will give us the space required between the characters.
In above formulas, it is necessary to add a comma after each argument or the function will not work.
Let’s see consider this example to understand more on how this function works:
Even if the cell contains more than one character, the CONCAT function will combine them as instructed. For example, for the following strings:
When we use the formula CONCAT(B11,” “,C11,” “,D11,” “, E11,” “,F11,” “,G11) it will combine all characters contained in cells B11, C11, D11, E11, F11 and G11 to give us the following character:
To understand the uses of this function, let’s consider a few examples:
Let’s assume you downloaded data about your suppliers in the following manner:
However, you need the entire name in one cell and in order of First name, Last name. In this scenario, CONCAT function can be used.
As we need the first name and then the last name, we would instruct the function to combine the strings in that manner. As discussed above, the formula to be used would be CONCAT(C5,” “,B5).
After typing the formula in D5, we can drag down the formula for all required cells using fill handle.
Let’s assume we need the name of suppliers but in capital letters. CONCAT can be used along with other Excel functions to get the desired results. Now in our example, we need the name Harry Potter to be combined but in upper case. In such scenario, we would use the formula:
The result will be the name in all capital letters:
Let’s assume in this example that the suppliers gave you the quotation per meter of the cloth you need as follows:
Now, you need the data as follows:
Harry Potter, $450/meter
In such scenario, the formula to use will be:
So here we used CONCAT formula to combine strings and create a sentence with the additional information.
Suppose we wish to add the name, email ID and address with line breaks, we need to use the CONCAT function along with CHAR function. Let’s see how:
The formula to use will be =C5 & ” ” & B5 & CHAR(10) & C2 & CHAR(10) & E5 & “, ” & F5 & ” ” & G5
In the above formula, we created a line break through CHAR 10 and merged values were separated using a comma (“, “) and space (” “).
The result will be:
We can use wrap text to ensure the results are displayed correctly as shown below:
As we saw above, if we do not use spaces in between the text to separate text entries, the text entries will run together. So, while using CONCAT function, we need to add that extra space. To add the space, we add double quotation marks with a space between them ” “.
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources:
To master the art of Excel, check out CFI's FREE Excel Crash Course, which teaches you how to become an Excel power user. Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.
Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!