Combine data from 2 columns into 1 column

Copper Contributor

I want to do this, but with 2 columns of data.  We have a sheet with area codes in one field and phone numbers in another.  I need the 2 pieces of data in one field for a list of 500 phone numbers.  Do I have to do them one at a time????

Combine data with the Ampersand symbol (&)

  1. Select the cell where you want to put the combined data.

  2. Type = and select the first cell you want to combine.

  3. Type & and use quotation marks with a space enclosed.

  4. Select the next cell you want to combine and press enter. An example formula might be =A2&" "&B2.

5 Replies

Use your formula in the initial cell and then drag the formula down to the last row. Put your cursor in the bottom right corner of the cell until it changes into a cross (+) and then click and drag to the bottom.

Or enter your formula into the first cell, select entire column range includes that cell and Ctrl+D

I took the following steps and had the following problem.


1. In the 1st Excel document, data in the first two columns was combined.

2. The combined data was entered into the third column.

3. The data in the third column was highlighted and copied.

4. The data in the third column of the 1st Excel document was copied and pasted into the first column of the 2nd Excel document.


The data did not paste.  Instead, the first column of the 2nd Excel document showed "REF" throughout.


How can I pasted the data into the 2nd document without getting "REF" instead?




[Admin removed phone number for privacy reasons]

[Admin removed email address removed for privacy reasons]



If by "Excel document" you mean an "Excel file", you can reference data from one file to another but those files cannot be relocated or renamed once referenced without opening each file, correcting the link(s), and re-saving.

If by "Excel document" you mean "Excel worksheet", this is possible but not by using copy/paste the way you are describing.

  1. On the first worksheet select the cells/column that contains the combined data you want copied to the second worksheet
  2. On the second worksheet right click the first you want to contain your copied data and under the pop-up "Paste Options" click "Paste Link (N)"

Note how the formula for each cell references the initial worksheet and cell.  Instead of doing a copy/paste you could just as easily have typed the reference formula for the first cell and then use the drag method to insert all the other references you want.

If you want only the values and not the formula to copy over (so that the pasted content is independent of the formula), you can select the newly merged column that contains the combined info and select Copy. When you paste into the new worksheet, select Paste (V) - which will paste just the value of each cell (i.e. the merged data) without the formula so it become an independent data set.

[Edit: updated who I was replying to; this answer was to Ben's question.]