Merging separate columns in Excel

By David Dagley | February 20, 2013 | Last updated on September 21, 2023
2 min read

Within an Excel document, you can merge data from two or more columns into one.

You can also split the data from one column into two or more columns if needed.

Suppose you have a Last Name and a First Name column that you want to merge into a single Name column. Follow these steps to complete this task:

  • Highlight the column to the right of the First Name and Last Name columns by clicking the column’s letter.
  • Create an empty column by selecting Insert>Columns on the Excel toolbar.
  • Type the word Name into the header row cell of the newly created column.
  • Select the first empty cell of the Name column (row 2) and type the equals sign (=) to start a formula.
  • Select the cell in row 2 of the First Name column.
  • Type &" "& (ampersand, quote, space, quote, ampersand).
  • Select the first data cell in the Last Name column. If Last Name is column A and First Name is column B, the formula should be =B2&" "&A2. formula
  • Then press the Enter key.
  • Select the first data cell of column C and rest the cursor on the bottom right corner of the cell until you see a black plus sign (+).
  • Drag the plus sign downward in the column to the end of the table.
  • When you release the mouse, the formula should fill all cells to the end of the table since you just copied the formula through the whole of column C, the new Name column,

If the Last Name and First Name columns are no longer needed and you want to delete them you will first have to make an adjustment to avoid an error in the Name column. This will occur because the formula created references cells in the two original columns.

Follow these steps to avoid this error:

  • Highlight the Name column by clicking its column letter (the letter C in this example).
  • Copy the column by pressing Ctrl + c.
  • Select cell C1, right-click and select Paste Special.
  • Select Values and click OK. paste special
  • Select the Last Name and First Name columns by clicking the column letter (A) and dragging right to column letter B.
  • On the Excel toolbar select Edit>Delete.

My next tech tip will outline how to split the data from one column into two or more columns.

David Dagley

David Dagley , CFP®, is a software developer and Adobe javascript expert, with 15 years of financial planning experience. He is the owner and president of Forms Doctor Inc., a company specializing in workflow automation solutions and financial planning calculators for investment dealers, financial advisors, and investment firms.