Table of contents :

Excel only keeps the data in the upper-left most cell, if you apply ' Merge & Center ' command (Home tab Merge & Center on the Alignment panel) to merge rows of data in Excel. Users have to use another method to merge multiple rows of data into one row without deleting data. See screenshot: (1) Select Combine columns under To combine selected cells according to following options; (2) Specify a separator for the combined data, here I select the Space option; (3) Specify the cell you want to place your combined result; (4) Specify how you want to deal with the combined.

  • Merge Cells tool keeps all data when the selection contains multiple data values. With Merge Cells you will never see the message 'The selection contains multiple data values. Merging into one cell will keep the upper-left data only' again. Your data in Microsoft Excel will never be lost!
  • Select the cells you want to combine, and click Kutools Merge & Split Combine Rows, Columns or Cells without Losing Data. Note: If you want to place the result in a new column or row, you need to select one more column besides the original data. Here I select one more column.
  • Click «Merge and Center». When the two cells are merged, only the data contained in the top left one is retained. So, if you need to retain all the data, then move it to the top left cell in our case, it's not necessary. Likewise, you can merge several cells vertically (a column of data). You can even merge a group of neighboring cells in.
Excel Merge Cells Keep Data

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.

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

Excel Merge Cells Keep Data

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

Merge Excel Columns Keeping Data

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.

Excel Merge Two Cells Keep Both Data

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

Related Function

Related Articles

Coments are closed

Most Viewed Posts

  • Hyperspin Retroarch
  • Wix Invoice
  • Poiel Sigil

Scroll to top