A CONCATENATE function in a single line of text, to group several strings, forming phrases and sentences, including using cell data from their Excel spreadsheets. You will know details of the function of this function and discover five interesting applications for it.
Knowing the structure of the CONCATENAR function
The syntax of the CONCATENATE function, which is able to group a total of up to 255 text strings into one, is very simple and practical. In it, only the first argument is required. The rest will depend on the number of strings that will be grouped. Let's see:
= CONCATENATE (TEXT1, [TEXT2], ...)
texto1: This is a required argument and will be the first item to be concatenated.
texto2: Argument optional. From this argument, all others will also be optional, observing the above mentioned limit.
All the items in the function must be separated by semicolons (;). Another way to get grouping of text strings is to replace the CONCATENATE function with the & operator. If we were to apply the function in the formulas A1 and B1, we could do it in the following ways:
= CONCATENATE (A1; B1)
= A1 & B1
Examples of CONCATENAR function applications in Excel
Now that we know its characteristics, we will know some possibilities of applying the CONCATENAR function:
Grouping first and last name
In our first example, we will group names and surnames into a table using the function.
The first argument will logically be the first name (which in our example is in cell D5). The next item has an important aspect: since we need to leave a space between the first and last name, the second argument will be exactly the white space (""). The third item will be the E5 cell, which is equivalent to the last name. Check out:
Thus, closing the parentheses will have the function structure ready. Just drag the other cells below and we'll have the first and last names in the same text string.
Concatenating pre-defined cell and text data
Another possible application is the use, in the same formula, of arguments extracted from cells and pre-defined texts. In this case, we will use the text string of the D5 cell in the first argument. In the second item, we will define the display of a dash (separated by empty spaces) and in the last argument the country (Brazil). Here's how the final result is:
Working with Date Series
There is also the possibility to apply the CONCATENAR function with series of dates. To be successful, you must add the TEXT function, thus creating a special formatting to prevent the Excel understand the dates as mathematical formulas. The syntax would look like this:
= CONNECT (TEXT (D5; "DD / MM / YYYY"); "-"; TEXT (E5; "DD / MM / YYYY"))
Check out our example:
Combining the CONCATENATE function with other functions
Thanks to its versatility, Excel allows the user to extract the maximum from their worksheets combining functions. In the next example, we'll show you how to apply the CONCATENATE and AVERAGE functions together.
Note that we first use the CONCATENATE function. In the first argument, we put the text "Final Average" and in the second the equality sign surrounded by empty spaces (":"). The novelty here is in the third item, in which we use the AVERAGE function to obtain the final result of the students. Here's how it went:
Applying the & operator
Finally, we'll show you how to apply the "&" operator instead of the CONCATENATE function. Using our previous example, we will delete the function name and the parentheses, replacing the semicolon with the operator. Since we are dealing with two functions, the parentheses for the mean remain. It will look like this:
You have learned in this article the structure of the CONCATENAR function and some of its possible applications. Take advantage of these features to boost your spreadsheets and get even more satisfying results. Share your experience with us in the comments and get to know our excel online courses.