CONCATENATE in Excel (Table of Contents)
CONCATENATE in Excel
CONCATENATE function in Excel CONCATENATEs (joins) several text strings into a single text string. Excel CONCATENATE Function at least takes one argument to return an output. It can take up to 255 arguments to combine the arguments into a single string. Excel CONCATENATE Function is categorized under Text functions.
CONCATENATE Formula in excel
Arguments other than the first one are optional, hence are enclosed with square brackets.
How to Use CONCATENATE Function in Excel
CONCATENATE in Excel is very simple and easy to use. Let understand the working of CONCATENATE in excel by some examples.
CONCATENATE in Excel Example #1
For example, if we have a list of first name and last name in column A and B and we want the full name that is the first name together with the last name in Column C, we will use the CONCATENATE Function in Excel.
CONCATENATE in excel joined the text of cell A2 with text in cell B2, but the full name is more readable if it contains a space in between the first name and the last name.
So, in that case, we can increase the number of arguments passed instead of two to three by placing the second argument in between text value in A2 and B2. The second argument that we will use is a string literal with a space within double quotes.
When we placed a space within double quotes after the comma after the first argument, the CONCATENATE Function in Excel took it as the second argument.
So, you can see whenever we pass an argument other than the reference value we have to always surround or enclose it within the double quotes because the MS- Excel and other office packages are written in C++ and in C++ the string literals are always taken within the double quotes.
So, if we directly write the string without double quotes, CONCATENATE in excel will not recognize it as a string and will throw an error #NAME?
In CONCATENATE Function in Excel, we can also pass the complete CONCATENATE Formula in excel as an argument. For example, if we want the result of two formula to be CONCATENATEd then we can pass the formulas as an argument to get the required output.
CONCATENATE in Excel Example #2
We have three tables with a list of employees. In first table we have their name and employee ID, in second table we have their names not arranged in the order as arranged in table 1, and their address city name and in the third table we have names not arranged in the order as arranged in table 1 and table 2. In the third table, we want to show employees ID joined with their city and separated by a hyphen.
We want the Employee id and city together but the names are not in same order in all the three tables, so we cannot directly use the CONCATENATE Function in Excel and pass reference values. If we do so, it will take a lot of time and matching. So, to do this task efficiently we can use the VLOOKUP function to lookup the employee id and city and CONCATENATE the values returned.
So, we will use the CONCATENATE Formula in excel in cell I2
Dragging the CONCATENATE Formula in excel downwards and applying it to each cell we have
Excel also uses an ampersand (&) as its concatenation operator and we can use it instead of excel concatenation function also with the same functionality and in a much simpler way.
Reverse of Concatenation
If we want to split up the CONCATENATEd values or if we want to split up texts into separate cells, in that case, we can use the Text to Column delimiter functionality available in excel. For example, if we want the employee id and city name separate in cells across the column, we can open up the Convert Text to Column Wizard (shortcut alt->a-e ), then choose Delimited, enter Next> and a choose a Delimiter from the list (Tab, Semicolon, Comma, Space), if any other delimiter check other: and specify and select general for text values and enter Finish. For example, we want to separate CONCATENATE and function
Step 1: Click on Data & then, text to columns, then choose Delimited, enter Next>
Step 2: choose a Delimiter from the list (Tab, Semicolon, Comma, Space) if any other delimiter check other: and specify
Step 3: select general for text values and enter Finish.
Limitation of CONCATENATE Function in Excel
Suppose, we have a list of text values in a column and we want to CONCATENATE all the text values into a single string value. So, if we use the CONCATENATE Function in Excel it takes up the arguments that are the text values one by one, and if the list of the argument is long it is not easier to pass it one by one to the CONCATENATE in Excel as it would take a lot of time and pain.
Hence, the CONCATENATE Function in Excel has its limitation that we cannot pass a range of values as an argument. CONCATENATE in Excel doesn’t work with range if we will pass range value it will pick up the value of the cell of the same row in which we write the CONCATENATE Formula in excel.
To overcome this limitation, a new function has been introduced in the latest version of the Excel named TEXTJOIN. It also CONCATENATEs the text values, but in a much easier way by taking up the range of values to be CONCATENATEd (A2:A14), rather than specifying each value one by one. It takes delimiter as the first argument and second argument checks for the empty cell if the cell is empty it take it as TRUE and ignores them and other arguments take the range of the values that need to be CONCATENATE.
You can download this CONCATENATE in Excel template here – CONCATENATE Function Excel Template
This has been a guide to CONCATENATE Function in Excel. Here we discuss the CONCATENATE Formula in excel and how to use CONCATENATE in Excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –
- AND Function in Excel
- VLOOKUP in MS Excel
- NOT in MS Excel
- Choose in MS Excel
- Match in MS Excel
- Trim in Excel