Merge columns to one column in excel

How To Merge Columns in Microsoft Excel Without Data Loss

If you are also struggling in combining a set of data in Excel and looking for a solution to merge cells or columns in MS Excel without losing data, then you have stumbled upon the right place. I’ll demonstrate few handy ways to merge columns in excel row-by-row into one.

For purpose of illustration, we have taken a sample table with First name, Last Name, and Grade. We will be merging the ‘First Name’ and ‘Last Name’ to single column ‘Full Name’.

4 methods to merge cells in Microsoft Excel without any data loss

If you don’t like reading the entire article, you can watch this YouTube video

Method 1: Merge Columns In Excel Using Concatenation Formula

• Firstly, to Insert a new column ‘Full Name‘ select the desired column header (in our case it is column D),
• Right click on it and select ‘Insert‘ option. We will rename this column as per requirement, in our case it is ‘Full Name‘.
• Now we will use the concatenation formula:В =CONCATENATE(B4,” “,C4)В where B4 is the “4th row of B Column” andВ C4В is the “4th row of C Column”.В

Now right click on the merged column and select ‘Paste Special’ option

• Now you can remove theВ two parent columns (First Name and Last Name) which are obsolete now.

And Done! You learned one method to merge multiple cellsВ in Microsoft Excel.

Method 2: Merge Columns In Excel Using Notepad

This is a little bit faster way to merge data in excel than using concatenation formula. However, the previous method is used to merge any columns, no matter if there is any space or column in between. MergingВ columns using notepad requires both the merging columns to be placed adjacent to each other.

Follow these steps to merge columns in excel using notepad.

• Hold Shift and select both the parent column headers you need to merge (First Name and Last Name in our case).

• Press CTRL+C on Windows or Cmd + C on MacВ to copy data in both columns.
• Now open Notepad or TextEdit on your desktop and hit CTRL+V.

• Now at any blank space, hit ‘Tab‘ key and copy the space created by this Tab operation. This operation is called copying of a Tab character. Alternatively, you can Press Tab and then hit CTRL+SHIFT+LeftArrow and then CTRL+X to copy a Tab character as well.

• Press CTRL + H to open ‘Replace‘ dialog box in Notepad or Fn + Cmd +F in TextEdit of Mac.
• In ‘Find What‘ field you have to paste the copied Tab character and just add your desired separator (space in our case) in the ‘Replace With‘ box. The separator can be space, a comma or any other symbol as per your requirement.

• Press CTRL + A / Cmd + A to copy entire new data in Notepad or TextEdit.
• Go to your spreadsheet and select the entire column and paste the newly merged data by pressing Ctrl + C or Cmd + C.

You can now delete previous obsolete columns and it won’t affect the merged column.

Voila! You have successfully seen a better way to merge cells in excel. This method seems tedious, but you will literally fall in love with the beauty of this method once you try it!

Method 3: Shortcut For Merging Cells Using Flash Fill

Microsoft Excel has built-in learning and adaptive systems. It keeps a track of work you do in a spreadsheet and can automatically suggest you auto-fill for the subsequent data field. By using Excel Flash Fill (Enable it if you haven’t already), you can easily merge multiple columns in excel. This can also be considered as “shortcut for merging cells in excel” in some lay-man terms.

Insert a new column in which you want to add the merged values to two columns.

• In our case, I wanted to club the ‘First Name’ and the ‘Last Name’ in the ‘Full Name’ column. So I did it in the first row (D3) of the Full Name column.
• Proceed to next cell and enter the data as required. By this time, you will see that Excel has understood what you intend to do and will suggest you an auto-fill. Hit Enter and your data from both the merging columns will be merged into one column.

• Delete the obsolete columns and you will be left with a single column with merged data in it.

If Flash Fill fails to suggest the matching pattern, then you can manually trigger it by pressing Ctrl + EВ or at Data -> Flash Fill.

This method is a lot simple since it doesn’t require any long copy-paste instruction set nor the use of any secondary application to carry out merging operation. Needless to say, you still have one more method to merge columns in excel and this time we’ll make use of third-party plugin.

Method 4: Merge Cells In Excel Using Third-Party Plugins

Alternatively, you can also use some third-party add-ons or plugins which can add a plenty of extra functions to your existing version of Microsoft Excel. I recommend “Kutools for Excel” which has various features to ease up your work and optimise productivity. You will be able to merge multiple cells in excel without losing data.

• Download “Kutools For Excel” from here.
• Install and add the plugin to your Microsoft Excel by following simple on-screen options.

• Click on “Combine” option under Kutools tab

Follow simple on-screen actions to justify your merged column location, separator symbol, and other short actions.

• Clicking Ok will instantly merge the selected cells and it is independent of parent cells.

• You can now delete the obsolete data and keep only the merged cells for better readability.

Kutools For Excel has a wide variety of capabilities which can help you save time while working with Excel.

Final Words

That’s it! You now know 4 efficient ways to merge multiple columns in Microsoft Excel without losing data. Do let us know which method worked best for you.

Spread the word and help us create better tech content

Источник

Merge and unmerge cells

You can’t split an individual cell, but you can make it appear as if a cell has been split by merging the cells above it.

Merge cells

Select the cells to merge.

Select Merge & Center.

Important: When you merge multiple cells, the contents of only one cell (the upper-left cell for left-to-right languages, or the upper-right cell for right-to-left languages) appear in the merged cell. The contents of the other cells that you merge are deleted.

Unmerge cells

Select the Merge & Center down arrow.

Select Unmerge Cells.

You cannot split an unmerged cell. If you are looking for information about how to split the contents of an unmerged cell across multiple cells, see Distribute the contents of a cell into adjacent columns.

After merging cells, you can split a merged cell into separate cells again. If you don’t remember where you have merged cells, you can use the Find command to quickly locate merged cells.

Merging combines two or more cells to create a new, larger cell. This is a great way to create a label that spans several columns.

In the example here, cells A1, B1, and C1 were merged to create the label “Monthly Sales” to describe the information in rows 2 through 7.

Merge cells

Merge two or more cells by following these steps:

Select two or more adjacent cells you want to merge.

Important: Ensure that the data you want to retain is in the upper-left cell, and keep in mind that all data in the other merged cells will be deleted. To retain any data from those other cells, simply copy it to another place in the worksheet—before you merge.

On the Home tab, select Merge & Center.

If Merge & Center is disabled, ensure that you’re not editing a cell—and the cells you want to merge aren’t formatted as an Excel table. Cells formatted as a table typically display alternating shaded rows, and perhaps filter arrows on the column headings.

To merge cells without centering, click the arrow next to Merge and Center, and then click Merge Across or Merge Cells.

Unmerge cells

If you need to reverse a cell merge, click onto the merged cell and then choose Unmerge Cells item in the Merge & Center menu (see the figure above).

Split text from one cell into multiple cells

You can take the text in one or more cells, and distribute it to multiple cells. This is the opposite of concatenation, in which you combine text from two or more cells into one cell.

For example, you can split a column containing full names into separate First Name and Last Name columns:

Follow the steps below to split text into multiple columns:

Select the cell or column that contains the text you want to split.

Note: Select as many rows as you want, but no more than one column. Also, ensure that are sufficient empty columns to the right—so that none of your data is deleted. Simply add empty columns, if necessary.

Click Data > Text to Columns, which displays the Convert Text to Columns Wizard.

Click Delimited > Next.

Check the Space box, and clear the rest of the boxes. Or, check both the Comma and Space boxes if that is how your text is split (such as «Reyes, Javiers», with a comma and space between the names). A preview of the data appears in the panel at the bottom of the popup window.

Click Next and then choose the format for your new columns. If you don’t want the default format, choose a format such as Text, then click the second column of data in the Data preview window, and click the same format again. Repeat this for all of the columns in the preview window.

Click the button to the right of the Destination box to collapse the popup window.

Anywhere in your workbook, select the cells that you want to contain the split data. For example, if you are dividing a full name into a first name column and a last name column, select the appropriate number of cells in two adjacent columns.

Click the button to expand the popup window again, and then click the Finish button.

Merging combines two or more cells to create a new, larger cell. This is a great way to create a label that spans several columns. For example, here cells A1, B1, and C1 were merged to create the label “Monthly Sales” to describe the information in rows 2 through 7.

Merge cells

Click the first cell and press Shift while you click the last cell in the range you want to merge.

Important: Make sure only one of the cells in the range has data.

Click Home > Merge & Center.

If Merge & Center is dimmed, make sure you’re not editing a cell or the cells you want to merge aren’t inside a table.

Tip: To merge cells without centering the data, click the merged cell and then click the left, center or right alignment options next to Merge & Center.

If you change your mind, you can always undo the merge by clicking the merged cell and clicking Merge & Center .

Unmerge cells

To unmerge cells immediately after merging them, press Ctrl + Z. Otherwise do this:

Click the merged cell and click Home > Merge & Center.

The data in the merged cell moves to the left cell when the cells split.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

Источник