CONCATENATE in Excel (Table of Contents)
CONCATENATE in Excel
Concatenate function is the most commonly used function in excel, which is used for combining the different cell values into one cell as a text string. For example, concatenate function can combine or join 30 text items into one cell and return the output as text. If your using MS Excel for a long time or ages, you might have seen the Concate function earlier, which is now replaced by Concatenate in current versions.
CONCATENATE Formula in Excel:
Below is the CONCATENATE Formula.
CONCATENATE function uses the below parameter
- text1: This is a required argument, and it is the first item to join. The item can be a text value, cell reference or a number
- text2: This is also a required argument and the additional text items that we wish to join. Thus, we can join up to 255 items that are up to 8192 characters.
How to Use the CONCATENATE Function in Excel?
This CONCATENATE Function is very easy to use. Let us now see how to use the CONCATENATE function in Excel with the help of some examples.
Example #1
Assume you have a list of employees. However, you have the first name in column A & the last name in column B. Your manager wants the full name of all the employees in the next 10 minutes. If you are a first time user, then you must have felt the hell in your workplace.
However, today I will demonstrate the CONCATENATE Function to combine the first and last names.
Now, we need to combine the first name with the second name to get the full name. If you apply the CONCATENATE Function, you can combine two values of two different cells.
The final result will be:
Now we have sorted the issue, but there is still a still issue to deals: then between the first name and the last name. So we need to concatenate the space after the first name to get this sorted.
4.8 (9,606 ratings)
View Course
The final result will be:
We need to place space with double quotes on either side of the space after the first argument to get an accurate result.
Whenever we pass an argument other than cell reference, we need to include that within the double-quotes. If we pass without a double quote, we get an error.
Example #2
We have three sales employees’ tables. The first table has their name, how many units they sold, the price per unit, and the total sales value.
In the second table, we have their names sorted from A to Z. In this table; the sales value is missing. Therefore, we need to extract the sales value from Table 1 using Vlookup.
In the third table, we have their names sorted from A to Z, and this table contains their efficiency level.
Now, we need to concatenate the Vlookup value with their efficiency level. For example, Andrews Sales is 9724, and his Efficiency is 78.56%.
We need to use CONCATENATE and Vlookup to get the accurate sentence here.
Firstly Concatenate function taking the sales manager as a reference and adding the word Sales is then, fetching the sales data of the sales manager by Vlookup and adding the value of and his then again fetching the remarks column value by using Vlookup.
=CONCATENATE(F3,” “,”Sales is “,VLOOKUP(F3,A3:D11,4,0),” and his “,VLOOKUP(F3,I3:J11,2,0))
Example #3
Instead of using concatenate function, we can use an ampersand (&) as its concatenation operator, and we can use it instead of the concatenation function with the same functionality and in a much simpler way.
The formula cell A1 adds the value of cell B1 to get the result as Ramya.
Example #4
Sometimes our data would need to be separated by a line break instead of spaces or characters, as shown in the previous examples. Consider the previous example only but show the sales manager efficiency level in the next line.
For Example, Andrews Sales is 9724.
Andrews Efficiency is 78.56%.
Since we need to add a line breaker, we cannot simply type the line breaker here; instead, we need to use the CHAR function to insert a new line to break the line. For example, CHAR (10) will add a new line breaker in excel.
To get the result, as shown above, we need to enable the “Wrap text” option for the results to display properly. To do this, use Ctrl + 1 to open the Format Cells dialog. Next, switch to the Alignment tab and check the Wrap text box or go to the Home tab and under Alignment, click on Wrap text.
=CONCATENATE(F3,” “,”Sales is “,VLOOKUP(F3,$A$3:$D$11,4,0),” “,CHAR(10),F3,” “,VLOOKUP(F3,$I$3:$J$11,2,0))
VBA Code to Use CONCATENATE Function
Like in Excel, we can use CONCATENATE function in the VBA code also. For example, the below code illustrates the usage of the CONCATENATE function in VBA macros.
Sub Concatenate_Function_Example Dim New_String as string New_String = “Combined” &” “ &”Text” Msgbox New_String End Sub
If you run the above code message box will display Combined Text as your result.
Things to Remember
- The reversal of the concatenate Function can be done by using a Text to Column converter.
- The result from the concatenate function will be as String.
- CONCATENATE Function converts numbers to text when they are joined by concatenating.
- The ampersand (&) is much quicker and faster than the concatenate function.
- Only text values need to be in double-quotes if a number needs to be mentioned in double-quotes.
- The function does not recognize arrays. Hence, we need to provide each cell reference separately.
- #VALUE! Error –arises when any one of the CONCATENATE function’s arguments is invalid.
- #NAME? Error – arises when any of the quotations marks missing from a Text argument.
- In recent versions of Excel, Microsoft has developed a new function called TEXTJOIN by looping through arrays.
- When you are selecting too many cells to hold the ctrl key, keep selecting the cells; it will automatically insert the commas after each new cell.
Recommended Articles
This has been a guide to CONCATENATE Function. Here we discuss the CONCATENATE Formula and how to use CONCATENATE function in Excel and practical examples and downloadable excel templates. You can also go through our other suggested articles –
