Combine

Enter 0 to cells without data: 2. Select the range and go to Data- Power Query/Data Query- From Table/Range: 3. In Query Editor select all Product columns and go to Transform tab- select Unpivot Columns, the same for Marge Product columns: 4. After Unpivot Columns go to Home- Close & Load, load data to Workbook. Excel is good for handling data, but there are times when you want to combine cells into one value or to split a string to make the data more manageable. Excel has several features which give us the ability to merge and split cells in multiple ways. Here’s how to merge cells in Excel and how to split cells in Excel. How to merge two or more cells.

Summary:

Does merging rows and columns in Excel seems a tough task for you to perform? Read this tutorial to learn different ways to merge rows and columns in Excel.

Excel

Microsoft Excel is a very useful application and can be used for performing various tasks. This is the reason Excel provides various useful functions to make the task easy for the users.

One of the most common tasks that everyone needs performing now and then is merging rows and columns.

But the problem is that performing this is not an easy task and Excel does not provide any tool to do this.

This is quite complicated as merging rows and columns in some cases causes data loss.

As while trying to combine two or more rows in the worksheet by making use of the Merge & Center button (Home tab > Alignment group), you will start getting the error message:

“The selection contains multiple data values. Merging into one cell will keep the upper-left most data only.”

And if you click OK, merged cells would contain just the value of the top-left cell and as a result, entire other data will be removed.

So this is what leads you to Panic situation!!!

To get rid of this, today in this article I am sharing different ways to easily merge rows and columns in excel without losing any data.

Below check out the fixes on how to merge rows in Excel or how to merge columns in Excel.

To recover Excel data without any data loss, we recommend this tool:

This software will prevent Excel workbook data such as BI data, financial reports & other analytical information from corruption and data loss. With this software you can rebuild corrupt Excel files and restore every single visual representation & dataset to its original, intact state in 3 easy steps:

  1. Download Excel File Repair Tool rated Excellent by Softpedia, Softonic & CNET.
  2. Select the corrupt Excel file (XLS, XLSX) & click Repair to initiate the repair process.
  3. Preview the repaired files and click Save File to save the files at desired location.

How To Merge Rows & Columns In Excel Without Losing Data?

There are different methods for combining row and columns text in Excel. Here check the ways one by one to merge data without losing it. First, check how to merge rows in Excel.

Part 1# How To Merge Rows in Excel

When it comes to merging the Excel rows there are two ways that allow you to merge rows data easily.

  1. Merge Excel rows using a formula
  2. Combine multiple rows using the Merge Cells add-in

1. How to Merge Multiple Rows using Excel Formulas

Excel provides various formulas that help you combine data from different rows. Possibly the easiest one is the CONCATENATE function. So here checks out some examples for concatenating numerous rows into one:

  • Merge rows with spaces between data: For example =CONCATENATE(B1,” “,B2,” “,B3)
  • Combine rows without any space between the values: For example =CONCATENATE(A1,A2,A3)
  • Merge rows > separate the values with comma: For Example =CONCATENATE(A1,”, “,A2,”, “,A3)

Now check how the CONCATENATE formula works on the real data.

  • On the sheet choose an empty cell and type the formula into it. Type the formula as per the data rows
  • And copy the formula across entire other cells in the row.
  • Now, simply you are having several data rows merged into one row.

2. How to Combine Rows in Excel using the Merge Cells Add-in

The Merge Cells add-in is used for merging various types of cells in Excel. This allows you to merges the individual cells and also combines data from entire rows or columns.

Please Note: You need to download a merge cell add-ins for third-party sites available online. Search in Google for add-ins.

Follow the given steps to combine two or more rows in your table:

  1. Choose rows you are looking to merge > click on the Merge Cells icon.
  2. Now the merge cells dialog window opens with a table or range selected already. And in the upper part of the window, you can see the three basic things:
    • How you want to join cells– For combining rows of data > choose “column by column“.
    • How to separate merged values with – an array of standard separators is available to choose from > comma, space, semicolon, anda line break. So select the separator as per your desire.
    • Where you need to place the merged cells > either the top cell or bottom cell.
  1. Now check the lower part of the Windows to check if you need any additional options:
    • Clear the content of selected cells – Choose this if need data to remain in the merged cells only.
    • Merge all areas in the selectionThis option allows you to merge rows in two or more non-adjacent ranges.
    • Skip empty cells and Wrap text – Well, these are self-explanatory.
    • Lastly, Create a backup copy of the worksheetThis option is checked by default. It is just a precaution that keeps you on the safe side and prevents the risk of data loss.
  1. Click the Merge button > to check the result – possible the merged rows of data separated by line breaks.

So, these are the two ways that allow you to merge rows in Excel without any data loss. Now, check out the ways on how to combine two columns in Excel.

Part 2# How To Merge Columns In Excel

Here check out the 3 ways to merge data from several columns into one without using VBA macro.

  • Merge two columns using formulas
  • Combine columns data via NotePad
  • The fastest way to join multiple columns

1. Merge Two Columns using Excel Formulas

1. Into your table > insert a new column > in the column header place the mouse pointer > right-click the mouse > select Insert from the context menu. Name the newly added columns for eg. – “Full Name”

Combine Multiple Columns In Excel

2. In the cell D2, write the formula: =CONCATENATE(B2,” “,C2). The B2 and C2 are the addresses of First Name and Last Name. And in the formula, the quotation marks “” is the separator that will be inserted between merged names any other symbol can be used as a separator e.g. a comma.

3. Just like this, join data from several cells into one by making use of any separator of your choice.

4. Simply, copy the formula to other cells of the Full Name column. If the First name or the Last name is deleted, then the corresponding data in the Full name Column will also be gone.

5. Next, try converting the formula to a value so that you can remove the unnecessary columns from the Excel worksheet. Choose entire cells with data in the merged column (choose the first cell in “Full Name” Column > press Ctrl +Shift + Arrow Down)

6. Now copy the contents of the columns to clipboard > right click on the cell in the same column (“Full Name”) > choose “Paste Special” context menu > choose “Values” radio button > click OK.

7. Now remove “First Name” & “Last Name” columns that are not required. Click the column B header > press and hold Ctrl > click column C header.

8. After that make a right-click on any selected columns > select Delete from the context menu.

9. This is it, now you have successfully merged the names from 2 columns into one.

2. Combine columns Data via Notepad

This is another way that allows you to merge several columns. Here you don’t need any formulas. This is suitable for combining adjacent columns to make use of the same delimiter for all of them.

For Example: If looking for combining 2 columns with First Names and Last Names into one:

  1. Choose both columns you need to merge: Click B1 > press Shift + ArrrowRight for choosing C1 > then hit Ctrl + Shift + ArrowDown for choosing entire data cells with data in two columns.
  • And copy data to clipboard > open Notepad > insert data from the clipboard to the Notepad
  • Then copy tab character to clipboard > hit Tab right in Notepad > hit Ctrl + Shift + LeftArrow > press Ctrl + X.
  • After that Replace Tab characters in Notepad with the separator, you require.
  • Hit Ctrl + H for opening the “Replace” dialog box > paste the Tab character from the clipboard in Find what field > type the separator Space, comma etc in “Replace with” field. Hit the Replace All button > to close the dialog box press Cancel
  • Now select the entire text in the Notepad and copy it to Clipboard.
  • Then switch back to Excel worksheet (press Alt + Tab) > choose B1 cell and paste text from Clipboard to your table.
  • And rename column B to Full Name and remove the “Last name” column.

So, this is the second way that allows you to merge columns in Excel without any data loss.

3. Join Columns Using Merge Cells Add-in For Excel

This is the easiest and quickest way for combining data from numerous Excel columns into one. Just make use of the third party merge cells add-in for Excel.

And with the merge cells add-in you can merge data from many cells by using any separator you like (for example carriage return or line break). With this, you can join row by row, column by column, or merge data from the selected cell into one without any loss.

There are many third-party add-ins online sites that allow you to download the add-ins and merge the cells easily in just a few clicks.

Conclusion:

So this is all about merging rows and columns in Excel without any data loss.

Follow the given steps to combine text in rows and columns easily.

Hope the given different steps will allow you to perform the task easily in the rows and column. Here I have described different methods of merging rows and columns data in Excel without any data loss.

So make use of anyone that you find easy for you.

However if in case you come to face any issue or data loss situation in Excel then make use of the MS Excel Repair Tool. This is the best tool that allows you to repair and recover data from the corrupted, damaged Excel file.

Additionally, you can learn advanced Excel to become more productive and easily utilize Excel functions and formulas.

5 Easy Methods To Merge Rows and Columns in Excel Without Losing Data
Description
Merging Excel rows and column is a very important task but if facing problem then read this article to rows and columns in Excel without losing any data...
Author
Repair MS Excel Blog
Publisher Logo

Margret

Margret Arthur is an entrepreneur & content marketing expert. She writes tech blogs and expertise on MS Office, Excel, and other tech subjects. Her distinctive art of presenting tech information in easy to understand language is very impressive. When not writing, she loves unplanned travels.

Excel is good for handling data, but there are times when you want to combine cells into one value or to split a string to make the data more manageable.

Excel has several features which give us the ability to merge and split cells in multiple ways. Here’s how to merge cells in Excel and how to split cells in Excel.

How to merge two or more cells

Every now and then in Excel, you'll need to merge two or more cells in a range. One example may be in the case where a dataset is spread across several columns, and for the sake of presentation, you may want to merge the cells containing the heading or title of that dataset to make them behave like one cell.

At other times, ‘merging a cell’ may mean extracting and actually combining data that are located across several rows or columns and placing them in another row or column.

Let’s talk about how to do both.

Download your free merge/split practice file!

Use this free Excel merge/split file to practice along with the tutorial.

Merge & Center

A quick and common way to merge cells is to use the Merge & Center command in the Home tab.

Simply highlight all the cells to be merged, then click the Merge & Center icon.

In the example above, cells A1 and B1 were highlighted then the Merge & Center command was selected. The result is that both cells now behave like a single cell, which is given center alignment. The cell alignment may be changed if desired.

Limitations of Merge & Center

  • It should be noted that Merge & Center is only a formatting command. Data from multiple cells will not be combined when using Merge & Center.
  • If there is data in the cells being merged, Excel will only keep what is in the upper leftmost cell. If you attempt to merge cells where data is in any other cell, Excel will return the error message, “Merging cells only keeps the upper-left value and discards other values.” In other words, you will lose any data that isn’t in the first cell.
  • Click OK to delete all other data and merge the highlighted cells.
  • Another limitation when you use the Merge & Center option to merge cells is that it also prevents you from being able to sort any data that is located in cells that have been merged.
  • Yet another issue is that highlighting a column that contains merged cells will result in highlighting all the columns spanning the merged cells, not just the column that was selected.

Alternative to Merge & Center

If you want to merge cells in different columns in a single row, the Center Across Selection offers a good solution. The command is not as easy to find in Excel as the Merge & Center option, which is a pity, but it does allow sorting and highlighting without any issues.

To use Center Across Selection:

  • Select the cells that you want to merge.
  • Press Control + 1 to open the Format Cells dialog box.
  • In the Alignment tab, from the Horizontal drop-down, select Center Across Selection.
  • Click OK.

Using this method will display the text as though it has been merged and centered, but each cell can still be selected one by one, separate from each other. Cells can also be sorted and highlighted as normal.

If data is in any cell other than the upper leftmost cell, there will be no loss of data. Instead, cells will appear as normal, as though they have not been merged.

Combine text from two or more cells into one cell

There are other times when “merging cells” refers to combining the actual data that is in multiple cells into one cell.

This can be accomplished through concatenation.

Three simple methods to concatenate or join values in Excel are shown below. They are:

  1. Using the concatenation operator (& symbol)
  2. Using the CONCAT function
  3. Using the TEXTJOIN function

Each of these methods is designed to join two or more text strings into one string.

Merge cells using the concatenation operator

Using the ampersand (&) symbol between values will join them in a string.

For example, in the dataset below, let’s say we want to have the full name of each individual shown in a single column, column C. Using the & symbol as a concatenation operator is a popular choice because knowledge of function formats is not required although this method is, technically speaking, a formula.

As with all Excel formulas, we would begin with an equal sign. Next, separate each value with the & symbol. Since a space is also likely desired between the first and last names, we would also enter a space within double-quotes.

Merge cells using CONCAT function

The CONCAT function allows the selection of a range and is therefore potentially quicker than using the & symbol, especially when no additional characters are required between the cell values being joined.

This would work well in the case of the dataset below:

The syntax of the CONCAT function is:

We can merge the contents of cells A2 to C2 in cell D2 with the entry:

If additional characters are required between cell values, these would be entered within double quotes and separated by commas as is usually the case with function arguments.

Merge cells using TEXTJOIN function

TEXTJOIN can be considered an improvement in the efficiency of CONCAT since it allows repetitive delimiters (characters between values) to be entered once.

The syntax of TEXTJOIN is:

The ignore_empty argument is required and is a setting that tells Excel what to do if empty cells occur within the range. If set to TRUE, empty cells are ignored. If set to FALSE, the delimiter is returned nonetheless, resulting in consecutive delimiters with no values in-between.

We can insert dashes between each cell value with the entry:

Combine Multiple Columns In Excel

Click here to learn more about using formulas to merge cells in Excel.

Split one cell into two or more

For those times when you’d like to split a single cell with data into two or more columns, the Text to Columns command may be just the thing you need.

Combine multiple columns in excel into one

For example, let’s say we have the following name list, and we want the names to be split across two columns.

Method 1 - Split cell using Text to Columns command

We would do the following:

  • Select the cells which contain the text to be split (A1:A15).
  • Click on the Data tab.
  • In the ‘Data Tools’ group, click the ‘Text to Columns’ command.
  • In the Convert Text to Columns Wizard:
    • Step 1 of 3: Select the Delimited radio button. This allows you to use a specified character to determine where the column break(s) should be.
  • Click Next.
  • Step 2 of 3: Select Space as your delimiter. A preview of what your data will look like is shown in the Data Preview section at the bottom of the dialog box.
  • Click Next.
  • Step 3 of 3: The Column data format section allows you to specify the format of each column as General, Text, or Date format. If there is a particular column that you do not want to be imported, select it in the Data preview pane and click ‘Do not import column (skip)’ in the Column data format section. The destination field tells Excel where to place the first cell in your new dataset. The default will always be the first cell of your original data. This, of course, means that your original data will be replaced. If you want to be able to compare your new dataset with the original, choose another cell as your destination. In this case, we will choose cell B1.
  • Click Finish.

The result is that the text in column A has been split across columns B and C, using the spaces as delimiters.

Method 2 - Split cell using Flash Fill command

With Flash Fill, you can teach Excel what you want your data to look like by entering the first two or three rows with the data in the desired format.

Next, click on the last value you entered, then click the Flash Fill icon from the Data tab in the Data Tools command group.

Combine 2 Columns Together

Repeat for each column, and voila! Your one-column data has been split across two columns.

Method 3 - Split cell using a formula

Finally, you can also use the LEFT, MID, and RIGHT formulas to break up the values in cells according to their position within the string.

For example, let us assume that we have the following dataset containing telephone numbers, which we would like to split into three separate columns as follows:

  • The area code, consisting of the first three numbers.
  • The prefix, consisting of the next three numbers.
  • The line number, consisting of the final four numbers.

The syntax of the LEFT function is:

Num_chars is the number of characters in text to return, starting with the leftmost character. If omitted, only the leftmost character is returned.

Combine Multiple Columns In Excel Into One

This formula asks Excel to extract the three leftmost characters from the string in cell A2.

The syntax of the MID function is:

Start_num is the position number of the first character to be returned, counting from the leftmost character in text.

Num_chars is the number of characters in text to return, starting with the leftmost character.

This formula asks Excel to extract three characters from the string in cell A2, starting with the fourth character from the left.

The syntax of the RIGHT function is:

Num_chars is the number of characters in text to return, starting with the rightmost character. If omitted, only the rightmost character is returned.

This formula asks Excel to extract the four rightmost characters from the string in cell A2.

Now we have successfully split the text in one cell into three cells using a formula.

Learn more

With all these different ways to merge and split cells in Excel, including the data in those cells, you’re bound to find one that suits your needs. Have you found any other methods useful? Let us know in the comments below.

You can also check out our course library to learn some other useful techniques in Excel. You can start with our free Excel in an Hour course to cover some basics. Then upgrade your Excel skills with our comprehensive Basic to Advanced Excel course.

Combine Multiple Columns In Excel Into 1 New Column

Learn Excel for free

Start learning formulas, functions, and time-saving hacks today with this free course!

Start free course
Coments are closed

Most Viewed Posts

  • Teams Personal Use
  • Top Prime Video Movies
  • Invoiceberry
  • Discord Onlyfans

Scroll to top