Tanjima Hossain
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 text from two or more cells into one cell 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 to merge text from two or more cells into one cell in Excel. Hence, read through the article to learn more and save time.
Download Practice Workbook
You can download the workbook used for the demonstration from the download link below.
7 Suitable Ways to Merge Text from Two or More Cells into One Cell
In this tutorial, I will show you how to merge text from two or more 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.
1. Merge Text from Two or More Cells Through Flash Fill Feature
First of all, you can use the Flash Fill feature to merge text from two or more cells into one cell 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.
- 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.
- Finally, press the Enter button and you will get the whole merged text into one cell.
Read More: How to Merge Cells in Excel Table (7 Ways)
2. Utilize Ampersand (&) Sign to Combine Text into One Cell
Above all, the Ampersand operator is one of the best techniques to merge text from two or more 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
- 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.
3. Insert CONCAT Function to Merge Text from Two or More Cells
Moreover, you can merge text from two or more cells into one 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. Hence, read through the steps mentioned below.
📌 Steps:
- In the beginning, select cell D5 and write down the following formula.
=CONCAT(B5," ",C5)
- In the end, hit the Enter key and utilize the AutoFill tool to the entire data.
Read More: How to Merge Cells in Excel with Data (7 Quick Ways)
4. Apply CONCATENATE Function to Combine Text
Additionally, by applying the CONCATENATE function, you can merge text from two or more cells into one cell 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)
- At last, press the Enter key.
Read More: How to Merge Two Cells in Excel Without Losing Data (2 Ways)
Similar Readings
- How to Merge Cells Vertically Without Losing Data in Excel
- Merge Data in Excel from Multiple Worksheets (3 Methods)
- How to Merge Multiple Cells in Excel at Once (3 Quick Ways)
5. Merge Text from Multiple Cells with TEXTJOIN Function
Furthermore, you can merge text from two or more cells into one cell 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)
- Lastly, hit the Enter key to get your final result.
Read More: How to Merge Multiple Cells without Losing Data in Excel (6 Methods)
6. Apply Power Query to Join Text
Moreover, you can use Power Query to merge texts easily. Usually, power query is a very useful tool and we use it on a regular basis. However, you need not use any function or formula in this method to complete the task. Hence, read through the steps below.
📌 Steps:
- Firstly, go to the Data tab and select From Table/Range.
- Secondly, a Create Table dialog box will appear where you must select the range to create a table and press OK.
- Thirdly, the Power Query Editor will appear where you have to select the First Name and Last Name columns.
- Fourthly, move on to Add Column and select Merge Columns.
- After that, the Merge Columns dialog box will appear where you can choose any type of Separator from the option and write any type of name in the New column name option.
- Here, I have selected Space as a Separator. Now, press OK.
- After all, go to File and select Close & Load.
- Finally, you will get your desired output. After some modifications, it will look like the image below.
7. Combine TEXTJOIN and CHAR Functions to Merge Text
Last but not least, merging texts from two or more cells into one cell becomes easier with the combination of TEXTJOIN and CHAR functions. Hence, follow the steps mentioned below in order to merge text cell.
📌 Steps:
- In the beginning, select cell D5 and write down the following formula.
=TEXTJOIN(CHAR(32),TRUE,B5,C5)
Here, CHAR(32) indicates Space.
- In the end, press Enter and use the AutoFill tool to complete the operation.
Read More: How to Merge and Center Cells in Excel (4 Easy Methods)
Conclusion
These are all the methods you can follow to merge text from two or more cells into one cell 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.com.
