CONCATENATE function in Excel is used to combine data or text from multiple cells or multiple sheets within a workbook into a single cell.
In this article, we are going to learn the different ways to concatenate data in an Excel sheet or workbook.
Let’s take an example to understand the use of this function.
#1: CONCATENATE in Excel Using Cell References and Text Strings
The table below shows the first names of team members in Column A and last names in Column B. Now, we want their full names in Column C.
The formula in cell C2 is entered as below:
=CONCATENATE(A2, “ “, B2)
Using this formula, we combine the first and last names from cells A2 and B2 and add a space (“ “) in between. Use the solid square at the right bottom edge of cell C2 to drag this formula down column C to obtain full names of all team members as shown below.
#2: CONCATENATE function in Excel Using Cell References, Strings and Formulas
Now, let’s say we add Middle Names of team members to the sheet as well and we want the First Name, Middle Initial and Last Name of each team member in column D. Our formula for this function will be:
=CONCATENATE(A2, “ “, LEFT(B2,1), “. “, C2)
We combine cell A2, add space, pick 1 alphabet from the beginning of the middle name using the Function LEFT, add a dot and space within the same inverted commas as text and then add cell C2.
#3: CONCATENATE in Excel Using Cell References, Strings, Formulas and Defined Formats for Text
Other than these simple functions, there is lots that can be done with the CONCATENATE function in Excel. Now we added the Month and year in Row A and Bonuses in Column E.
Now, let’s say we want to print the name and bonus of the highest performer for the month in row 12. We use the formula below:
=CONCATENATE(“The top performer for the month of “,TEXT(A1,”mmm-yy”),” with the highest bonus of $”,TEXT(MAX(E3:E9),”#,##0.00″),” is “,INDEX(D3:D9,MATCH(MAX(E3:E9),E3:E9,0)),”.”)
Note that we can nest other formulas within the CONCATENATE function to obtain the desired result.
In the formula above, we concatenate the string which we want to keep as a standard text. Then, we add month and year in a format that we defined as “mmm-yy”. The, we add another string, include the formula for calculating the highest bonus from column E. Then we use the word “ is “ to knit the sentence further and finally write the formula to determine the index (full name from Column D) of the team member who received the highest bonus. Lastly, the statement ends with a full stop within inverted commas.
Conclusion
In conclusion, statement concatenation for strings and data using the CONCATENATE function in Excel can be done in various ways. First, we can just use the cell names and even define the format in which we want the data to appear. Second, we can use any text written within inverted commas (“ “). Third, we can also use computation formulas to build a statement or combining data.
Interested in learning more Excel skills with Swiftly Skilled. Check out this interesting tutorial on IF, AND, OR, NOT Operators in Excel here.
