How to use concatenate in Excel?
CONCATENATE is a function used in Excel to combines the two strings or parameter values into a single string. As the name implies, this function is used for string concatenation. It is a widely used function. You can also say that – concatenation is a process of combining two or more columns/cells’ data.
In Excel worksheets, the data does not always place according to your needs and requirements. Sometimes, you need data divided into multiple cells or combined data of two or more columns. You have to do some manipulation on data to get the data according to you. For all this type of stuff, you need to perform different operations on an Excel worksheet.
Excel offers CONCATENATE function to get this work done. In this chapter, you will learn how one can combine and get the data of two or more columns using CONCATENATE function. We will brief you complete working of CONCATENATE function and usage.
What is concatenate in Excel?
Usually, concatenate term refers to combining the data. In Excel, there are two ways to combine the data of an Excel spreadsheet:
- Using CONCATENATE function (Concatenate cells value)
- Using & operator
Each method is equally useful and used when needed as per the user requirement.
Method 1: Using CONCATENATE function
To concatenate the cells data in an Excel worksheet, CONCATENATE function is used. CONCATENATE is a function, which is used to combine the cell’s data. Whenever this function is used, it merges the value/data of the cells.
In other words, you can also say that – concatenation is a process of combining two or more columns/cells’ data. Excel provides CONCATENATE function that helps the users to combine some piece of text that resides in different cells. We will describe this method in detail with an example below.
Here, we have the syntax of CONCATENATE function –
Syntax
Besides that, you can also use any white space between the cells data as defined in the below syntax. E.g.,
Return Value
The CONCATENATE function always returns a text string after combining the data.
It means that the resultant data will always be a text string even if the cell is containing number data.
Steps to concatenate the cells data
Follow the steps to concatenate the cells data using CONCATENATE function:
Step 1: Open the Excel file of which you would like to merge the cells data. Here, we have this Excel worksheet.
Step 2: Select a cell where you want to paste the concatenated data result after performing CONCATENATE operation on cells.
Step 3: Go to the Formula bar to paste the CONCATENATE formula. In this formula bar, paste the CONCATENATE formula to merge data for cells A2 and B2 here.
Step 4: Use the following formula and combine the data.
For example, =CONCATENATE(A2,B2)
It simply will combine both cell’s (A2 and B2) data without using any whitespace or comma between them. Customize the cell number accordingly.
Step 5: Here, you can that the data of cells A2 and B2 have been successfully combined. But you will see that data has been merged without having any whitespace.
Example 2: Merge data with whitespace or comma
We have another example for merging cell data. In this example, we will use a third parameter (whitespace) between the cells so that the resultant data we will come by merging with whitespace. Although you can also use comma (,) as well instead of whitespace as needed.
Follow steps to merge the cells data containing with whitespace using CONCATENATE function:
Step 1: Follow step1 to step 3, same as followed in Example 1.
Step 2: Inside the formula bar, copy and paste the following formula to merge the cells data along with whitespace between them. Customize the cell number accordingly.
For example, =CONCATENATE(A3,” “, B3)
Now, this data the result (merge data) will be came by combining both cells (A3 and B3) data having whitespace between them.
Note: You can see that we have put single whitespace inside the double-quotes in this formula.
Step 3: Press Enter key to see the result. Here, you can that the data of cells A3 and B3 have been successfully combined and having no whitespace.
Some other merges will be like as showing below for different parameters.
Things to remember while using CONCATENATE function
- It can join any type of data, either string or number depending on the cell containing it.
- Number data will be converted to the text when merged using CONCATENATE function.
- The CONCATENATE function always returns a text string after combining the data, which means the resultant data will always be a text string even if the cell contains number data.
- CONCATENATE function can join 30 cell data together. You can concatenate a total of 8,192 characters (up to 255 strings).
- The ampersand operator (&) is an alternative to CONCATENATE. You can also use this to combine the cells data in Excel instead of using CONCATENATE. We will discuss it later in detail.
- If one of the arguments passed in the CONCATENATE function is invalid, it returns #VALUE!
Method 2: Using & operator
It is another way to combine the Excel cells data. Basically, the ampersand operator (&) is an alternative to CONCATENATE function. You can also use this to combine the cells data in Excel instead of using CONCATENATE.
This method is quite simple and handy method to concatenate the strings. Use of the ampersand (&) operator is easy and quick. It does not require a lengthy formula to concatenate the string. Although, it is almost similar to the CONCATENATE operation and the results are also the same for both.
Here, we have syntax for concatenating the cells –
Syntax
For example, =A1&B1 to concatenate the cell A1 and B1 data.
Let’s understand with a practical example directly with an Excel worksheet:
We have an Excel worksheet having some data in it. Here, we will concatenate data for columns A and column B.
Steps to concatenate the cells data using &
Follow the steps to combine the cells data using & operator.
Step 1: Open the Excel file of which you would like to merge the cells data. Here, we have this Excel worksheet.
Step 2: Select a cell where you want to paste the concatenated data result after performing CONCATENATE operation on cells.
Step 3: Navigate to the Formula bar just above the column number at the right side of Excel. Here, writes the formula for concatenating the two or more cells.
Step 4: Now, use the following formula and press Enter to combine the A2 and B2 cell data successfully.
It will return and paste the data into the selected cell after concatenating the data.
Step 5: Here, you can that the data of cells A2 and B2 have been successfully combined. But without having any whitespace or comma.
Example 2: Merge data with whitespace or comma
Now, we will take one more example for merging the cells data, but this time having the third parameter, like comma, whitespace, or followed by any other string.
Basically, in this example, we will pass one more parameter (whitespace) between the cells so that the resultant data will come by merging with whitespace. Although you can also use comma (,) as well instead of whitespace as needed.
Follow the steps to merge the cells data containing with whitespace using & operator:
Step 1: Follow step1 to step 3, same as followed in just above example.
Step 2: Inside the formula bar, use or copy and paste the following formula to merge the cells data along with whitespace:
For example, =A3&” “&B3
This formula will return the concatenated result (merge data) by combining the cell A3 cell B3 data having whitespace between them.
Note: You can see that we have put single whitespace inside the double-quotes in this formula joined it with & operator with cells.
Step 3: Press Enter and get the result. Here, you can that the data of cells A3 and B3 have been successfully combined, having whitespace between them.
Some other merges will be like as showing below for different parameters.
CONCATENATE vs & operator
However, both methods return string text after concatenating the specified string. Most Excel users often confuse between then which one is more efficient. There is not a big difference between both methods. They have only one different mentioned below:
| CONCATENATE | & operator |
|---|---|
| CONCATENATE function limits that only 225 strings can be concatenated using this function, which means that around 8,192 characters. | On the other side, there is no limitation when using & (ampersand) operator. |
This is the only difference between them. Since 255 is a big number, there is no need to combine such a huge number of strings in real-life. So, you can use any of these formulas that you find simple and easy.
Besides that, there is no other difference in CONCATENATE and & working.
Merge Cells
Basically, this way is not a right way to combine the cell data. However, it provides outcome almost similar to the CONCATENATE function, but it merges the cells not the cells value/data. Sometimes, this method works but not always. If this method does not fulfill your requirement, you can use the above method.
Merge cell method actually does not combine the cells data; it exactly combines only the cells, which look like concatenated data. It is a quick method to get the data merged without using any formula. Excel allows the user to use this method in different ways, such as –
- Merge rows
- Merge columns
- Merge multiple cells
Generally, combing the cells data using the merge cell method, we lose the data. Because it only keeps the upper left value, it means the first column value and rest will be discarded.
CONCATENATE vs Merge Cell
Both CONCATENATE and marge cells are completely different in function, although the results are similar. CONCATENATE function combines the cells data while the Merge cells method merges the cells to combine the data. Note the difference that merge cell method actually does not combine the cells data; it exactly combines only the cells, which seems like concatenated data.
The following diagram will demonstrate the difference between these above two methods. It will give you a clear understanding that how these methods work differently.
