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.

Dataset for using excel formula to combine cells

method

1. Applying Merge & Center Option

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.

Merge & Center as excel formula to combine cells

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

method

2. Using Merge & Center Option for Multiple Cells Without Losing Data

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.

Merge & Center for multiple cells with excel formula to combine cells

  • Finally, we will get the result.

method

3. Applying Ampersand Symbol to Merge Cell with Text

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.

Approach

3.1 Combine Cells with Space / Comma or Separator in Between

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.

=C6 & ” ” & D6

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

excel formula to combine cells

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

Fill Handle tool in excel formula to combine cells

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

Approach

3.2 Combine Cells Without Space / Separator in Between

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.

=C6 & D6

excel formula to combine cells without space

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

Approach

3.3 Combine Cells with Line Breaks in Between

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.

=B6&CHAR(10)&C6&CHAR(10)&D6&CHAR(10)&E6

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

excel formula to combine cells with a line break

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

method

4. Using CONCATENATE Function to Combine Multiple Cells

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(C6,” “,D6)

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

CONCATENATE excel formula to combine cells

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

method

5. Applying Justify Feature to Combine Text from Two or More Cells into One Cell

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.

Justify feature for excel formula to combine cells

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

method

6. Using Text Formula to Combine Text in Excel

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.

=”Birthday of ” & B6 & ” ” & C6 & ” is ” & TEXT(D6, “dd/mm/yyyy”)

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

TEXT function in excel formula to combine cells

  • 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.

Using the Find & Select option to find merged cells

  • 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.

Unmerge combined cells using Merge & Center tool

  • 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.

(Visited 21 times, 1 visits today)
Md. Ahaduzzaman

Md. Ahaduzzaman

Hello everyone i'm Md.Ahaduzzaman.I am a civil engineering graduate from BUET.I am enthusiastic and highly motivated to always learn something new to broaden my horizon and find my lacking. Hopefully my Articles will benefit you to understand and overcome your problem.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo