What Does CONCATENATE in Excel Mean?
Concatenate function in excel is used to concatenate or join two or more than two characters or strings or numbers together, concatenate function is a substitute for using the & operator to join as & operators made the syntax look more complex while concatenate function looks more clean and easy to understand.
Arguments other than the first one are optional; hence they are enclosed with square brackets.
How to Use the CONCATENATE Function in Excel? (with Examples)
CONCATENATE is very simple and easy to use. Let us understand the working of CONCATENATE in excel by some examples.
For example, if we have a list of first names and last names in columns 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.
CONCATENATE 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 took it as the second argument.
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
So, you can see whenever we pass an argument other than the reference value, and 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 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 formulas to be concatenated, then we can pass the formulas as an argument to get the required output.
We have three tables with a list of employees. In the first table, we have their name and employee ID, in the 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 the 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 the 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 checks 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.
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, 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 as it would take a lot of time and pain.
Hence, the CONCATENATE Function has its limitation that we cannot pass a range of values as an argument. It doesn’t work with a range; if we pass the 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 the 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 the second argument checks for the empty cell; if the cell is empty, it takes it as TRUE and ignores them, and other arguments take the range of the values that need to be CONCATENATE.
Concatenate Excel Function Video
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 –