Merge 2 Columns In Excel Without Losing Data

  1. Merge 2 Columns In Excel Without Losing Data Download
Table of contents :

To merge two cells or more without losing data, you cannot use the “Merge Cells” command because Excel only keeps the data in the upper-left cell, you lose data in another cell.

The solution is to use a formula or other solution that is easier and faster, with no formula losing no data. For more details, read the explanation below.

This brief overview of the XLTools Combine Cells Data for Excel shows:- How to join, combine and merge cells without losing data- How to combine rows, column. Select Column A and B and delete them. So yeah its done. You have merged two columns without loosing any data, successfully. How to Merge Two Columns Without Losing Data in Excel. Excel Shortcut Keys for Merge and Center.

Solution #1, Using TEXTJOIN Function

The excel TEXTJOIN function is the best function provided by Excel to merge multiple cells without losing data. Regardless of the number of cells to be merged, TEXTJOIN function requires only the range address, no need to type all the cell addresses to be merged.

The Formula

The next step is to delete the “First Name”, “Middle Name” and “Last Name” columns.

An error appeared, the full name data that was already available properly disappeared instead with #REF! Error. This error occurs because the “Full Name” column contains a formula. You must convert the formula to the text, do this by doing a Copy (CTRL+C) – Paste Special (CTRL+ALT+V) – Values.

Please see the video tutorial below, for step by step:

For solutions with other functions, please read the following article:

Solution #2, Using Flash Fill Menu

Flash Fill is no formula solution. Type 1 combined data with the desired format in the first row. Type the combined data again in the second row, after two or three letters; the combined data list appears for all rows.

If the displayed data is as desired, press the ENTER button. You merge cells in excel without a formula. Delete the “First Name”, “Middle Name” and “Last Name” columns, because they are made without a formula no #REF! error will appear.


Solution #3, Using NOTEPAD


Yes, you can use NOTEPAD to combine multiple columns into one column. Do a copy (CTRL+C) in range A2:14, the range containing first, middle and last name then paste (CTRL+V) in NOTEPAD.

There are blank spaces with random width between first, middle and last name. The blank space is a TAB character. Use the “Replace” menu to change the TAB character to space. To get the TAB character, do a copy in a blank space between first and middle name.

The results are copied back then paste in Excel.

Which One is the Best Solution?

The TEXTJOIN function is a new function, available in Excel 2019 or Excel 365 Subscription. If you don’t have this function, and still want to use a formula, you can consider other functions.

For no formula solutions, you can choose to use “Flash Fill” or NOTEPAD. Choose the most comfortable solution for you.

Related Function

Related Articles

Excel will give you a warning message telling that you will lose the data, when you try to merge cells using the Merge & Center feature. In this article, we’re going to show you how to merge cells in Excel without losing your data.

  1. Begin by adding a new column where you want the merged column to be placed.
  2. Select the first cell and create a formula CONCATENATE formula, adding a separator if necessary. In this example, we used a space (” “).
    =CONCATENATE(C2,” “,D2)

Merge 2 Columns In Excel Without Losing Data Download

  1. Copy down the cell to apply to all cells in the column.
  2. When all cells in the new column are selected, copy the cells by pressing Ctrl + C. Alternatively, you can click the Copy item from the right-click menu.
  3. After copying, open the right-click menu again. This time click the Paste as Values button to replace formulas with static values.
  4. Since we got rid of the formulas, we do not need the old columns., and they can be safely removed. You can delete them by selecting the columns and clicking the Delete button from the right-click menu.
  5. You will end up with the merged column.