How to Merge Cells in Excel and Keep All Data (2024 Update)
In this article, we will show you how to merge cells in Excel and keep all data. Simply follow the steps below.
Merge Cells in Excel and Keep All Text
To merge cells in Excel while keeping all data, you can use three different methods. Choose the one that best suits your needs.
Merge Cells in Excel and Keep All Text Using CONCATENATE Formula
Follow the steps below to merge cells into one while keeping all data using the CONCATENATE formula (or CONCAT function) in Excel.
1. Insert a New Column
If you have a table with client information and want to combine two columns (First & Last names) into one (Full Name), first, insert a new column. Right-click on the column header where you want to add the new column (e.g., column D), and select “Insert” from the menu. Name this new column “Full Name”.
2. Write the Formula in the Designated Cell
In cell D2 (assuming the new column starts from D2), write the CONCATENATE formula: =CONCATENATE(B2,” “,C2)
Or if you’re using Excel 2016 – Excel 365, you can use the CONCAT function:
=CONCAT(B2,” “,C2)
Here, B2 and C2 represent the addresses of First Name and Last Name, respectively. Remember to include a space between the quotation marks ” “ to separate the merged names.
3. Copy and Paste the Formula to Keep All Data
Once you’ve written the formula in cell D2, simply copy it. You can do this by selecting cell D2, then pressing CTRL+C (or right-click and select “Copy”).
Then, paste the copied formula into all other cells in the “Full Name” column. You can do this by selecting the cell range where you want to paste the formula and pressing CTRL+V (or right-click and select “Paste”).
Merge Cells in Excel and Keep All Text Using TEXTJOIN Function
Another method to merge cells in Excel while keeping all data is by using the TEXTJOIN function.
1. Select a Blank Cell
Choose an empty cell where you want the merged data to appear. In the example below, we select cell D2.
2. Enter the TEXTJOIN Formula
Write the following formula in the selected cell: =TEXTJOIN(” “,TRUE,B2:C2) After entering the formula, press Enter to execute it.
3. Review the Output of the Merged Cells
The merged data will be displayed in the selected cell, separated by a comma and space.
Merge Cells in Excel and Keep All Text Using Notepad
Alternatively, you can merge cells in Excel and keep all the data by utilizing Notepad. Here’s how it works:
1. Copy the Data to Notepad
Select the data you want to merge, press CTRL+C to copy, and then paste it into an empty Notepad.
2. Customize Data in the Notepad
Modify the data in Notepad as needed, such as arranging names on one line or inserting separators like commas.
3. Copy Modified Data
After customizing the data in Notepad, copy it. This will copy all the data you keep.
4. Paste Data Back to Excel
Paste the modified data back into an empty row in your Excel file by double-clicking.
We hope you now better understand how to merge cells in Excel and keep all data. If you enjoy this article, you might also like our article on How to Merge Cells with Same Value in Excel or our article on How to Unfreeze Panes in Excel.
