Combine Cells and Columns in Excel with Merging

Excel, unfortunately doesn’t have a button to push to Merge two columns and still maintain the data. However, there are three different ways to combine columns. The easiest is to use the Merge Cells Add-In, you can use formulas to merge two columns into a third column, and lastly utilizing the notepad to combine two adjacent columns.

Let’s start with the most time-consuming method, merging two columns using formulas.

time consuming method

Click to enlarge

To create a new column right click on the column immediately to the right of where you want the new column to appear and select Insert. For this example we are going to combine Column A “Last Name” and Column B “First Name” into the newly inserted Column C “Full Name”.

The formula to combine the two Columns is

=CONCATENATE(B2," ",A2)

CONCATENATE

Click to enlarge

It is found in the Formulas Tab of the Ribbon, select MORE FUNCTIONS, click Compatibility and select CONCATENATE. This formula is used to combine several cells into one cell. Using the formula listed we are telling Excel to enter the first name (B2) followed by a space (“ “) and then the last name (A2). If you wanted the full name to be listed as Last Name, First Name enter =CONCATENATE(A2,”,”B2).

A quick way to copy the formula all the way down Column C is to double-click the bottom right corner of the cell with the formula in it or click once and drag it down the column. Now you have the two columns combined in a third column. If you aren’t going to delete the information from the first two columns you are done. However, if you want to delete Column A or Column B the formula in Column C will need to be converted into a value, otherwise all data will be lost once you delete the two Columns.

  • asterisk
    Select all cells from Column C by clicking and highlighting the entire column or select C2 and hit (Control + Shift + Down Arrow) at once to select all cells within the column.
  • asterisk
    Copy to the clipboard using (Control + C)
  • asterisk
    Right click into any cell within Column C and select Paste Special and Values.
  • asterisk
    Column C cells are now value cells and are no longer linked to Columns A or B so those columns may now be deleted by clicking the Column Title and hitting Delete.
Delete format

Click to enlarge

Though this method requires several steps and can be time intensive depending upon the amount of data you are dealing with it does provide a few benefits. The first being the columns you are combining do not have to be next to each other and the ability to add the space, comma or any other separator between the two pieces of data. The second method for combining two columns is a bit faster.

Using the Notepad to merge two cells

In this example, we are going to combine the City and State Columns. Using the Notepad you can only combine columns that are next to each other.

1. Select D1 and highlight it along with E1 then hit (Control + Shift + Down Arrow) to select every cell within the two columns and (Control + C) to copy the cells.

Highligh table

Click to enlarge

2. Open Notepad from your Start menu and paste the contents inside.

note pad

Click to enlarge

3. For proper formatting, locate the TAB in between City and State and hit (Control + C) to copy it. Open the Find and Replace box using (Control H) and copy the Tab into Find. In the Replace box include a common and a space then select Replace All. This should eliminate the Tab in between the City and State and change the formatting to become “City, State”.

4. Highlight all the data within the Notepad by typing (Control + A) and (Control +C) to Copy all of the data. Switch back to Excel and (Control + V) paste the copied data into a new column or into one of the merged columns. If you create a new column you can keep the originals or delete the unused columns.

highlighted data

Click to enlarge

Using the Merge Cells Add-In

The third and easiest way to Merge two columns is by getting the Add-in for Merge Cells. In the Ribbon select Add-ins if you have already downloaded the Merge Cells Add-in it will be found under My Add-ins, however, if you have not you will need to select Store and search for it.

add-in

Click to enlarge

  • star-half-empty
    Select the two columns you wish to Merge and under the Add-Ins Tab in the Ribbon select the Merge Cells Add-in to open it.
  • star-half-empty
    Select whether you would like to Merge by Row (this is what we want to merge the Last Name and First Name), Merge by Column or Merge all selected into one cell.
  • star-half-empty
    Separate values by a line break, a space, or Other. Place a comma within the other box to separate the data by Last Name, First Name.
  • star-half-empty
    The Options are to clear the processed cells or Delete Empty cells. We will clear the processed cells.
  • star-half-empty
    The last option is where you would like the data placed, to the left or to the right of the selected data.
  • star-half-empty
    Click Merge Cells and the warning (below) will pop up making sure you understand that any formulas will be turned into values. Select YES and the cells will be merged.
Confirmation

Click to enlarge

Using Add-ins for Merging Cells is by far the fastest and easiest method to merge two columns within Excel.

Merging Titles and Headers

Merging cells is frequently used each time a title is to be centered over a certain section of a spreadsheet. You are able to combine several cells to be able to combine data or improve the appearance of the spreadsheet. It is also called concatenating cells or columns. In the next example below, I'm using the “Merge & Center” icon to the merged cell. Please browse the article.

To merge a group of cells using Merge & Centre Button, follow these 2 easy steps:

Step 1: On your new spreadsheet, select the text cells you want to combine. Here in this example, I have selected the title—Monthly Expenses (range of cells C5 to G5) to merge and align center.

Expense sheet

Click to enlarge

Step 2: Now click the “Merge & Center” icon placed on the toolbar and you’ll have the output like this.

Merge Center

Click to enlarge

Unmerge your Merged Cells

You can even split merged cells. Find the cell which was previously merged. Go back to the toolbar. Select "Merge & Centre" icon. Remember, You cannot split cells that were previously unmerged.

Auto Flash Fill in the Merged Column

When you have a lot of rows of data wherever you wish to combine text, there isn't to replicate the above steps of every row. Like, if Column C includes peoples' first names and Column D has each of their last names, and you wish to load order F with each of their first and last names combined, you can simply begin writing the combined text in order F and Excel 2010 may complete the remainder for you. Here get easy steps again…

  • share-square-o
    Press the cell wherever you wish to set the initial set of combined text.
  • share-square-o
    Type the combined text.
  • share-square-o
    Press ENTER
  • share-square-o
    Type another set of combined text. Excel can tell you a preview of the remaining portion of the column stuffed in along with your combined text.
  • share-square-o
    To accept the preview, press ENTER.

Before you go, get my report '10 things I did with Excel that increased my salary from £27K to £100K'

About the Author Sohail Anwar

Leave a Comment:

7 comments
Combine Cells and Columns in Excel with Merging – Earn and Excel says October 4, 2017

[…] post Combine Cells and Columns in Excel with Merging appeared first on Earn and […]

Reply
Tinashe Paradza says October 5, 2017

Great stuff. Keep the world learning excel

Reply
    Earn & Excel says October 5, 2017

    Thanks Tinashe, I appreciate that 🙂

    Reply
jim says October 25, 2017

really terrible advice from someone who doesn’t know Excel very well
this man may have done well for himself but is not good at Excel

Reply
    Earn & Excel says October 25, 2017

    Lol, you’d be surprised at the different workflows that work for different folks, this is a fairly beginner focused article Jim. I want to give a few options for different scenarios. In terms of not being good at Excel, my mum would disagree with you 😉
    Appreciate the time to comment Jim, stick around it will get more interesting…
    Cheers

    Reply
      jim says October 25, 2017

      sorry, I should have tempered that slightly, it wasn’t supposed to be a personal attack
      merging cells can cause many problems; if you must, only use them for the final presentation (and even then, don’t)
      Centre Across Cells (if you can find it!) is often a better option but Good Design is the best solution (eg use Tables, don’t hide rows and columns nor insert blank ones) – when it becomes second nature, it takes no longer and saves you, and others, time and frustration

      and never use CONCATENATE(), unless you really enjoy mindless typing; =A2&” “&B2 does the same job (your example but with the space added correctly )

      rants over – I feel better now

      Reply
        Earn & Excel says October 25, 2017

        Fantastic rant Jim! Good insights for everyone to benefit from. Cheers!

        Reply
Add Your Reply