In my early days of using excel, I knew not what concatenate was. If I needed to combine text from two cells, I would manually copy from each cell and combine in a new cell. That was until my colleague told me about Concatenate function. I was fascinated, and going forward I dedicated my time to learning and exploring excel and its features. My new Mantra became: if you are wasting time with excel, then you are doing it the wrong way.
In today’s post, we would be exploring the concatenate function and how to apply it to your data set in excel. The concatenate function allows you combine both written texts within the formula and texts written in a separate cell. We would touch on all these in the sections to follow:
Concatenate Syntax
The concatenate syntax is = CONCATENATE(text 1, text2, text3….)
The text represents arguments for the function, and the concatenate function accepts multiple arguments up to a total of 30. The texts could be manually inputted or selected from a cell to output as a text. Not to worry, we would briefly go through both methods in the sections to follow.
You should however note that you do not literally need to separate each single word as a new text. You can type in numerous amounts of words as text 1 for example before separating with a comma.
Concatenate Hardcoded Arguments
To use the Concatenate function with hardcoded text, all you need to do is to enclose the text with double quotations within the formula and separate each new text by a comma.
To be realistic, you probably wont need the concatenate formula to merge hardcoded text because you can as well type it out without the extra stress of adding a function and sticking to the rules of the function. The usefulness of the concatenate function is fully displayed when you need to merge from various cells, and we would be treating this in the next section
Concatenate Selection from Cells
To merge content of various cells, use the Concatenate formula and separate the cells selected with a comma. However, you may need a space between text in each cell, we would show you how to apply that space in our example below.
Use the Concatenate formula to get the text in cells A, B and C merged
You can achieve the same result above using the ampersand (&)
The above combinations fall short of one thing, and that is the fact that all three words are combined without spacing. How do we make ammends? Easy, just include a blank space enclosed in double quotations just before each cell seletion (except the first cell A1 of course)
Of course this can also be achieved with the ampersand (&) by including the same blank space enclosed in double quotations as we did above.
Combining hardcoded & Cell Selection
To illustrate the combination of hardcoded and cell selection whilst using the Concatenate function, we have a more complicated example. In this example, we are working on an option analysis for HR to decide commission payout for our sales team using performance benchmarks. We have 5 different positions within the sales unit (Analyst, Specialist, Manager, Head and Director) and 3 options.
Our task is to create payout % for each position based on their level of performance. However we want the headings for each column to be automatic depending on the percentages we set for option1, option 2 and option 3. Confusing yeah? well just check the screenshot below to better understand.
For each option we have different bands, and each position has different pay for achieving a particular band. For example, in Option 1, if a Manager achieves between 50% to 74.9% of his target, he gets paid only 70% of his salary. Our heading for each option is selecting from the input benchmark percentage in rows 2:8 using the concatenate function. This means that if we decide to change the bands, then our heading should automatically update.
In the above, you can see that the TEXT function is used to format the numbers to appear as a one decimal percentage (0.0%). To combine hardcoded text and text from a cell, simply separate with a comma and you are good to go.
CONCAT Function in Excel
The CONCAT function is the successor of the CONCATENATE Function. The main difference between the 2 functions is that the CONCAT Function allows you select a range of strings as any of the Value arguments. The CONCATENATE Function on the other hand can’t handle selecting a range of strings as a value. Let’s compare the two functions with an example below.
In our example, we have an email address separated into 4 contiguous cells, let’s see what each function would output when we select the range of 4 cells as our Value 1.
To combine all cells with the CONCAT Function, we would use the formula below:
Result:
Now let’s try doing same with the CONCATENATE Function using the formula below:
=CONCATENATE(A2:D2)
Result:
As you can see above, the CONCATENATE Function returns an array. Infact, the exact facsimile of the original data set as it is also spread across 4 contiguous cells.
In summary, this is the area in which the CONCAT Function edges out the CONCATENANTE Function. Regarding other functionalities, they are about equal.
