6 Examples of Using Excel Formula to Combine Cells

Sometimes in a dataset cell values are very far from each other. But we may need to combine them together to get complete information about that cell entry. So every now and then combining multiple cells is required. Also to make a dataset meaningful and interactive we merge as many cell values as possible so that we can learn about the whole attribute for an entry just by looking at a single cell. So if you want to know the Excel formula to combine cells this article will be helpful.

📁 Download Excel File

Download the Excel file below.

Learn to Use Excel Formula to Combine Cells with These 6 Examples

Here in this article, we are going to discuss six different methods to use excel formulas to combine cells. We have used different datasets for different cases. But a sample dataset for our demonstration is as follows.

Here in this step, we will apply the Merge & Center option to combine cells. So the step-by-step procedures are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly we select the cells we want to merge.
• Following that we press the Merge & Center option.

• Finally, we will see that cell B3 to E3 is merged and centrally aligned.

Here in this section, we are going to learn how to use Merge & Center option for multiple cells with data. So the steps are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly we again select the cells we want to merge.
• After that, we go to the Merge & Center option.

• A dialogue box will appear. We select OK.
• It is noticeable that this method only merges the upper left values only.

• Finally, we will get the result.

Here in this section, we will see three different ways to apply the  Ampersand symbol for three different cases. So the brief description is as follows.

Here in this step, we will learn to combine cells with space in between. So the steps are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly we go to cell E6 and write this formula. Ampersand adds value together.

• Here ” “ adds space between cell values C6 and D6.

• After that, we use the Fill Handle tool to drag the formula.

• Finally, we will get the combined cell values with a space in between in column E.

Here we will learn how to use an excel formula to combine cells without any space in between. So the step-by-step procedures are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly we got to the formula box of cell E6 and write this formula.

• Then we use the Fill Handle tool to drag the formula.
• Finally, we will get the result in column E.

Here in this section, we will learn how to use an excel formula to combine cells with line breaks in between. So the steps are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly we go to the cell F6 formula box and write this formula.

• Here the & symbol adds the cell value.
• CHAR(10) adds a line break between each cell value.
• Finally, we apply Wrap Text.

• After that, we use the Fill Handle tool to drag the formula.
• Finally, we get the result in column F.

Here we will apply the CONCATENATE excel formula to combine cells. So the steps for that are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly we go to the cell E6 formula box and write this formula.

• CONCATENATE function adds the three texts C6, ” “, & D6 together.

• Following that we use the Fill Handle tool to drag the formula.
• Finally, we get the result in column E.

Here we will learn how to apply the Justify feature for the Excel formula to combine cells. So the step-by-step procedures are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly we select the cells we want to combine.
• Following that, we go to the Editing option.
• After that, we go to the Fill dropdown option.
• Finally, we select the Justify option.

• Now we will see that our cell data are combined together in row 5.

Here we will learn how to use the excel TEXT formula to combine cells. Therefore the step-by-step procedures are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly we go to the formula box of cell E6 and write this formula.

• Here the & symbol adds the cell values together.
• TEXT(D6, “dd/mm/yyyy”) converts the cell value D6 into dd/mm/yyyy format.

• After that, we use the Fill Handle tool to drag the formula.
• Finally, we get the desired result in column E.

How to Find Merged Cells Quickly Using Find and Select Tool

Here in this section, we will learn how to find the merged cells using Find & Select tool. So the steps are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly we go to the Editing option.
• After that, we select the Find & Select option.
• Following that we select the Find option.

• Now we get a dialogue box. We go to the Replace option.
• After that, we select the Format option.

• We get another dialogue box. We go to the Alignment option.
• After that, we select Merge cells.
• Then we press OK.

• We press the Find All option now.
• Finally, we get all merged cells in the dialogue box.

How to Unmerge Combined Cells in Excel

Here in this section, we will learn how to unmerge the combined cells in Excel. So the steps are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly we select the cells we want to unmerge.
• After that, we go to the Merge & Center option.
• After that, we select the Unmerge Cells option.

• Finally, we will get the unmerged cells in row 3.

📄 Important Notes

🖊️  When we use Excel functions we have to use the function arguments carefully.

🖊️  To see the line break we have to use the Wrap Text feature after applying the function.

📝 Takeaways from This Article

📌  We can apply Merge & Center operation to combine cells.

📌  We can also use this option for multiple cells with contents.

📌  We can use the Ampersand symbol to combine cells.

📌  CONCATENATE function is also useful in combining cells.

📌  We can apply to Justify feature to combine cells.

📌  Apart from all these, we can apply the TEXT function to format data while combining cells.

Conclusion

Here in this article, we have learned six different ways to use the excel formula to combine cells. We can use options like Merge & Center from the Ribbon or we can also use different Excel functions like CONCATENATE, TEXT, etc. We can use any of the methods we like. You can comment in the comment box if you have any queries regarding this article. For further Excel-related problems and solutions, you can visit our website Excelden.