Excel’s “Merge Cells” function works like “Merge & Center” – without the addition of centering. Select the cells you want to combine and then click “Merge Cells.” The text in the original cell will not be automatically centered, and will remain left or right-justified. How to merge cells in Excel without losing data? Neighboring cells can be merged in vertical or horizontal direction. The result will be one cell occupying two rows or columns simultaneously. The information appears in the center of the merged cell. Merging cells in Excel step by step: Let's take a small table with several rows and columns. Combine multiple columns without losing data and number format in Excel Normally when merging cells with the Merge feature in Excel, it will remove all cell contents except the first cell content. However, with Kutools for Excel 's Combine (Rows & Columns) utility, you can easily batch combine multiple cells/rows/columns without losing data. How to Merge Rows in Excel. There is no quick and easy trick or feature to merge rows in Excel. You’ll need to use the same CONCATENATE function as you did for merging columns. However the technique is a little different. It isn’t very common for people to combine data from different rows into a new row, but the need may arise occasionally.
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.
To fill sequential data into Excel with the fill handle, we need not type the function in every cell in the column. We can quickly copy the function to the other cell. Excel merge cells vertically without losing data. The quick and simple way to combine two or more cells vertically is to use the built-in merge option combined with center option.
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 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.
Excel Merge Rows Without Losing Data Without
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.