CONCATENATE in Excel (Table of Contents)
CONCATENATE in Excel
CONCATENATE Function is a part of text function. CONCATENATE function is useful when you want to merge multiple cell values into one. Returns the combined value of multiple cells.
For Example, CONCATENATE (“Sachin” &” “&”is the”&” “&”God of Cricket”) in this formula CONCATENATE function will return the value of Sachin is the God of Cricket. So, CONCATENATE adding different values together.
CONCATENATE function is also available in VBA too. We will discuss that at the end of this article.
CONCATENATE Formula in Excel
Below is the CONCATENATE Formula:
CONCATENATE function uses 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. 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 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 is 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 your work place.
However, today I will demonstrate the CONCATENATE Function to combine the first name and the last name together.
Now, we need to combine the first name with the second name to get the full name. If you apply CONCATENATE Function, you can combine two values of two different cells.
Final Result will be:
Now we have sorted the issue but still, there is one more issue to deals that is the space in between the first name and the last name. We need to concatenate the space after the first name to get this sorted.
Final Result will be:
We need to place a space with double quotes on either side of the space after the first argument to get the accurate result.
Whenever we pass an argument other than cell reference, we need to include that within the double quotes. If we pass without double quote, we get an error.
Excel Example #2
We have three sales employees’ tables. In the first table, we have their name how many units they sold, price per unit, and the total sales value.
In the second table, we have their names sorted from A to Z. In this table, sales value is missing. 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 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 concatenation function with the same functionality and in a much simpler way.
The formula cell A1 adding the value of cell B1 to get the result as My Name is Ramya.
Example #4
Sometimes our data would need to be separated by a line break instead of spaces or character 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 line breaker we cannot simply type the line breaker here, instead, we need to use CHAR function to insert a new line to break the line. CHAR (10) will add 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. Switch to the Alignment tab and check the Wrap text box or go to 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 VBA code also. 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 about CONCATENATE Function
- The reversal of the concatenate Function can be done by using 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 concatenate.
- The ampersand (&) is much quicker and faster than the concatenate function.
- Only text values need to be in double quotes if it is number need to mention 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 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 along with practical examples and downloadable excel templates. You can also go through our other suggested articles –
