Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Merge Multiple Cells in Excel (5 Suitable Ways)

Fortunately, many of us use Excel in our business organizations. In any business organization, we use Excel to organize data as per need and make databases for the future. Moreover, one interesting thing is that we can merge multiple cells easily in Excel. However, I have used Microsoft Office 365 for the purpose of demonstration, and you can use other versions according to your preferences. In this article, I will show you 7 suitable methods of how to merge multiple cells in Excel. Hence, read through the article to learn more and save time.

Here, I have used the Ampersand (&) sign in the following figure to merge cells B5 and C5 in Excel. This is one of the 5 methods.

Use Ampersand (&) Sign to Combine Multiple Cells in Excel


Download Practice Workbook

You can download the workbook used for the demonstration from the download link below.


5 Suitable Ways to Merge Multiple Cells in Excel

In this tutorial, I will show you how to merge multiple cells into one cell and the process becomes more interesting with Excel. However, the task is easy and simple. But you will need an arrangement in order to perform the operation properly. Here, I have used 7 different methods to make you understand the scenario properly. For the purpose of demonstration, I have used the following sample dataset. Here, the dataset contains the First Name and Last Name columns and I will show the Full Name.

Sample Dataset to Merge Multiple Cells in Excel


1. Utilize Merge & Center Option to Merge Cells (This Discards Upper Right Values)

First of all, you can utilize the Merge & Center option to merge multiple cells in Excel. The process is simple and easy. However, you can get merged cells but it will provide only the upper-left value and discard the other values. So, if you need all of them then escape this method. Now, follow the steps below.

πŸ“Œ Steps:

  • First and foremost, select the cells (B5:D5 here) you want to merge.
  • Following, go to the Home tab and select Merge & Center from the merge option.

Utilize Merge & Center Option to Merge Cells

  • Now, press OK from the dialog box.

merge & center dialog box

  • After that, you will find a merged cell containing only the content of cell B5.

merge & center in excel

  • Similarly, repeat the process for each row in order to get your desired output.

use of merge & center in excel

Note:

  • Firstly, only the content from the topmost left cell remains by this procedure.
  • Secondly, you can also merge cells vertically in this way. During this, the merged cells will take the topmost value to show.
  • Thirdly, if you merge multiple rows and columns, it will show only the upper-left value.
  • Afterwards, if you don’t want to lose any data, follow the rest of the methods described below.

2. Merge Multiple Cells Through Flash Fill Feature (in Excel 2013 or Later Versions)

Thereafter, you can use the Flash Fill feature to merge multiple cells in Excel. However, it is a very easy and simple method and it provides the result automatically if it is turned on. Otherwise, it can be done manually by pressing Ctrl + E.

πŸ“Œ Steps:

  • First, write the combined text of the two columns in cell D5.

Merge Multiple Cells Through Flash Fill Feature in Excel

  • Second, start to write the combined text in cell D6. Now, you can see that Excel is showing the merged text for all the cells.

flash fill in excel

  • Finally, press the Enter button and you will get the whole merged cells into one cell.

use of flash fill feature in excel


3. Use Ampersand (&) Operator to Combine Multiple Cells

Above all, the Ampersand operator is one of the best techniques to merge text from multiple cells into one cell in Excel. Moreover, it is easier and more convenient to use. However, you need not use any function or formula in this method to complete the task. Hence, follow the steps below.

πŸ“Œ Steps:

  • Initially, select the cell where you want to merge the cells.
  • Then, type = and click on the first cell you want to combine.
  • Next, type & and click on the second cell. Here, I have used the below formula for the sample data.

=B5&" "&C5

Use Ampersand (&) Sign to Combine Multiple Cells in Excel

  • After that, press the Enter key and you will get the merged data.
  • Finally, apply the AutoFill tool to the entire column in order to get the final output.

Use Ampersand (&) Sign in Excel


4. Insert CONCAT or CONCATENATE Functions to Merge Multiple Cells

Moreover, you can merge multiple cells in Excel by using the CONCAT function. It is quite simple to apply in Excel. Usually, this function combines the data from multiple strings. However, this is a newer version of the CONCATENATE function.

4.1 Using CONCAT Function

  • In the beginning, select cell D5 and write down the following formula.

=CONCAT(B5," ",C5)

Insert CONCAT Function to Merge Multiple Cells in Excel

  • In the end, hit the Enter key and utilize the AutoFill tool to the entire data.

Insert CONCAT Function in Excel


4.2 Applying CONCATENATE Function

Additionally, by applying the CONCATENATE function, you can merge multiple cells in Excel without losing any data. However, this is an earlier version of the CONCAT function and you can use this function instead of CONCATENATE function if you have compatibility issues. But this version will remain compatible with earlier versions of Excel. Here, I will merge text from multiple rows into one cell and follow the instructions below.

πŸ“Œ Steps:

  • First of all, select cell C11 and insert the following formula.

=CONCATENATE(B5," ",C5,", ",B7," ",C7)

Apply CONCATENATE Function to Merge Cells in Excel

  • At last, press the EnterΒ key.

Apply CONCATENATE Functionin Excel


5. Use TEXTJOIN Function to Merge Multiple Cells with Delimiter

Furthermore, you can merge text from multiple cells by using the TEXTJOIN function in Excel. However, it is a similar process. Here, I will merge text from multiple rows into one cell. Hence, go through the steps below to complete the task properly.

πŸ“Œ Steps:

  • Initially, select cell C11 and write the following formula.

=TEXTJOIN(",",TRUE,D5,D7)

Merge Multiple Cells with TEXTJOIN Function in Excel

  • Lastly, hit the Enter key to get your final result.

TEXTJOIN Function in Excel


Note:

Merging data from multiple cells becomes easier with the combination of TEXTJOIN and CHAR functions. Hence, follow the steps mentioned below in order to merge text into one cell.

πŸ“Œ Steps:

  • In the beginning, select cell D5 and write down the following formula.

=TEXTJOIN(CHAR(32),TRUE,B5,C5)

Combine TEXTJOIN and CHAR Functions to Merge Multiple Cells in Excel

Here, CHAR(32) indicates Space.

  • In the end, press Enter and use the AutoFill tool to complete the operation.

Combine TEXTJOIN and CHAR Functions in Excel


Conclusion

These are all the methods you can follow to merge multiple cells in Excel. Overall, in terms of working with time, we need this for various purposes. I have shown multiple methods with their respective examples, but there can be many other iterations depending on numerous situations. Hopefully, you can now easily create the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.

For more information like this, visit ExcelDemy.

Mehedi Hasan

Mehedi Hasan

Hi, I am Mehedi. I have completed my B.Sc. from Bangladesh University of Engineering and Technology. I have a strong interest in innovation and research in the field of Data Science and Machine Learning. Gradually, I now understand the value of Data Analysis and I am trying to learn everyday.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo