We usually enter values in different or adjacent cells in the Excel spreadsheet. Because, an Excel spreadsheet is made up of rows and columns, both having cells in it. In some cases, we want to group values from different cells in order to get combined data. At that time, we can make use of this CONCATENATE function. It helps to join values from different cells in the worksheet and returns the result as text.
Today, we will let you know the purpose of CONCATENATE function and how to use it in the Excel worksheet with proper examples. Get an official version of MS Excel from the following link:
https://www.microsoft.com/en-in/microsoft-365/excel
Jump To
What is the CONCATENATE Function?
CONCATENATE is one of the built-in functions in Microsoft Excel. It comes under TEXT functions. We can use this function in our spreadsheet whenever we want to join values together. With this function, we can join up to 30 values. And it returns the result in text format.
1) Syntax
- This is the syntax of the CONCATENATE function, in order to apply it, enter the formula in your Excel cell.
- Hit the Enter button.
=CONCATENATE(text1, text2, [text3], …)
2) Arguments
- text1 – It is the first value to join together.
- text2 – The second value to join together.
- text3 – It is an optional one. The third value is to join together.
3) Motive
- This function will join values together in a cell.
4) Return Value
- It returns the joined text.
What Will CONCATENATE Function Do?
As we said earlier, the CONCATENATE function combines or joins the values together and returns the result as text. This function is categorized as “TEXT” in Excel. You need to know that, it will join up to 30 values.
Step 1: For instance, we have come up with the set of first and last names of employees working in an organization. In order to join those values together, we can use this CONCATENATE function.
Step 2: To join the given first and last names, we need to apply the following formula in any cell where we want to get the result.
=CONCATENATE(B3,C3)
Step 3: Now, click on the Enter button to get the result.
Step 4: To fill down the remaining cells, you just drag down the first cell. It will auto-fill the remaining cells as shown in the below screenshot.
We hope that the given scenario helped you to understand the CONCATENATE function. Kindly move to the below section, if you have any confusion about using it on your spreadsheet. We have given a workable example to understand it easily.
Use Separator to Join Values
Here we will see how to use a CONCATENATE function with a separator like a comma in a simple manner.
1) In the following screenshot, we have given values in Columns B, C, & D. In order to join those values together with the separator (,), we need to use the CONCATENATE function.
2) In order to get the corresponding results, we need to apply the following formula.
=CONCATENATE(B3, “, “,C3, “, “,D3)
3) Hit the Enter button to get the result, as shown in the below image.
4) To fill down the remaining cells, you just need to drag down the first cell. Excel will autofill the remaining cells with the CONCATENATION function.
By doing so, you can quickly join multiple values together in a single cell in the spreadsheet. Hope the above-given example is enough for you to understand it. Excel also provides an alternative for this function which is called “CONCAT.” You can find this feature in Excel versions 2019 and later.
Citations
To understand the usage of CONCATENATE function, we have referred to the following articles. They helped us to know the purpose of this function. You can also check out them using the given links: CONCATENATE function[1], and What is Concatenation?[2].
- https://corporatefinanceinstitute.com/resources/excel/concatenate-function/
- https://en.wikipedia.org/wiki/Concatenation
Summary
With the help of the given scenarios and examples, you can easily understand the purpose of the CONCATENATE function in Excel and how to apply it in the spreadsheet. The screenshots help to learn it even easier. Drop your feedback in the below comment section. Thanks for visiting AawExcel. Keep Learning!!
If you want to know what people think about this CONCATENATE Function of Excel, then we suggest you look for it on the Quora interaction webpage. From there you can get to know more about the Excel CONCATENATE function. Here, we have provided the link to Quora’s official website.
Video Tutorial
The following video will show you how to use the Excel CONCATENATE function in the spreadsheet with proper examples.
Read Ahead:
- How to Use Excel FLOOR.MATH Function?
- How to Use Excel FLOOR Function?
- How to Use Excel CEILING.PRECISE Function?
- How to Use Excel CEILING Function?
- How to Use Excel GCD Function?
