Excel Combine cells

Combine cells in Excel (Table of Contents)

• Combine cells in Excel
• Examples of Combine cells in Excel
• How to Use Combine cells in Excel?

Combine cells in Excel

Ever wondered how we can combine the content of two or more cells into one cell? This happens many times that when we have a set of data that is not as per our requirement and we have to combine the content of one and more cell into one cell to display the desired information.

Suppose if we have a data in which we have “The first name” in one cell and “last name” in another cell, in this case, if we want the complete name in a single cell for any reason, we have to combine the cells and here the function of concatenate is to be used.

Concatenate is the inbuilt function of Excel that requires no hard coding or VBA help to run. This function helps to combine the content of more than one cells into a single cell and maintains the data also in the original form.

This function is mainly used to combine values from more than one cell to display data in a more useful form as in case of below example.

Concatenate function is different from using the function of the merged cell as the function of the merged cell will combine only cells and not the data of the cells. If we merge cells than we only physically merge the cells and not the data as in below case, where only the cells are merged and not the data.

Examples of Combine cells in Excel

Example #1

• Combine cells in excel without spaces

If we want to combine cells without any spaces then we can simply use “&” to combine cells as below.

This is the result of combine cells without any spaces which is shown below.

• Combine cells in excel with space

We can add space after the content of the first cell by simply adding the space in double Quotes.

This is the result of combine cells with space which are shown below

• Combine cells in excel with a line break

A line break can be added by specifying the character value of line break that is 10.

This is the result of combine cells with line break which are shown below

• Combine cells in excel with a static Text.

This is the result of combine cells with static Text which is shown below

• Combining a date and text

A date cannot be combined with a text in a cell, this can be only done by using the Text function of Excel.

This is the result of combining date and text which is shown below

• Combining data with a comma.

This is the result of combining data with a comma  which is shown below

Explanation

When it comes to combining the cells than concatenate function is the easiest way to combine the data into one cell. This function can save from working overnight by quickly combining the data from the cells.

Concatenate function can be used by the formula or by simply using the “&” sign. The data that can be combined from the cells can be in any form, it may be

• Text
• Date
• Special characters
• Symbols

By using the concatenate function the data can be easily combined into one cell as shown in the above examples. The only difference is this that the concatenate function comes with the limitation of 255 characters. However, there is no such restriction when it comes to using the symbol “&” for combing the data.

How to Use Combine cells in Excel

Combining of cells can be done in any way, this can be done via the formula of concatenating or by using the symbol of “&”

Method 1^st by using the function of concatenating.

• Step 1^st

First, select the cell where you want the combined data to be displayed.

• Step 2^nd

Go to the ribbon and select the option of formulas and then select the option of insert function.

• Step 3^rd

Simply type the formula name and click on ok to insert the formula of concatenate.

• Step 4^th

Give the reference of the cells from where the data is to be combined and click on Ok.

• Step 5^th

This is to show how Concatenate function is used in the cell where you want the combined data.

Below is the result of function and combined data.

Method 2^nd by using the “&”.

“&” can be simply used to combine the data if in case the concatenate function is not used.

• Step 1^st

Begin typing with the “=” sign and then select the first part of the text. Now insert “&” and then select the next part of the text and click enter.

• Step 2^nd

Below will be the output after enter is pressed.

Things to Remember about combining the Cells

1. If we are using the concatenate function of Excel then this is required that there is at least one text argument that is given to the function.
2. This should be remembered that Excel has its own limits, this means that a maximum of 255 strings can be combined by using the concatenate function or they should not be not more than 8,192 in characters.
3. Even if we are combing the cells that have numbers, date or any format. The result will always be text.
4. Combining cells via concatenating will not work if we are giving array reference to the formula of concatenating. This means that the cells references should be entered individually instead of giving an array to the function.

This means that to combine the cells from A1 to A5 we must write the formulas as

= concatenate (a1, a2, a3,a4,a5) and not as =concatenate(A1: A5)

5. If any of the cells that are ought to be combined has an error than the result will be an error. For example, if cell A1 has an error and we have given reference of this cell in any of the concatenate formula than the result will be an error. So this is important to note that no cell that is ought to be combined has the error.
6. If in case a cell has a date and we want to combine this cell with any other cell than in this case we have to use the “text” function of Excel. A cell that has the date in it cannot be combined by simply using the concatenate, this is because the end result of concatenating is a text.
7. Combing the cells via merged cells options will only combine the cells and not the data that is in the cells. This is because merged cells option only physically combines the cells and is not meant to combine the data of the cells.
8. A static text that is to be used while combing the data should be enclosed in Quotation marks, else the formula will be an error. For example, we must write “= concatenate (“text”, A1)” instead of writing “=concatenate(text, A1).

Recommended Articles

This has been a guide to Combine cells in Excel. Here we discuss the Combine cells in Excel and how to use the Combine cells in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. Excel MIN Function: Guide
2. Excel CODE Function: Examples
3. Guide on AGGREGATE Function in Excel
4. Examples of Scrollbar in Excel