Combine data from 2 columns into 1 column

[radfordsix]

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.

[#2]

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.

[#3]

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

[#4]

I took the following steps and had the following problem.

Steps:

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.

Problem:

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

Question:

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

Thanks,

Ben

[Admin removed phone number for privacy reasons]

[Admin removed email address removed for privacy reasons]

[#5]

@bencredosforkidscom 

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.

[#6]

@bencredosforkidscom 
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.]

[#7]

Thanks! I had the same issue as Ben above. Paste special > Paste Link was the perfect fix!!

[#8]

Doesn't work. Example?@radfordsix 

[#9]

@JessyVision 

Example is here Combine text from two or more cells into one cell - Microsoft Support

[#10]

@SergeiBaklan 1. Here is my error

You can see the formula just doesn't work. Yes, I do have automatic calculations selected.

2. The example link you provided also doesn't work. Yes, my internet is fine (or I wouldn't be able to post this comment).

[#11]

Hey all I used it by using "Flash Fill" - here is the example video that helped me: https://www.youtube.com/watch?v=1-U4RAj9qec

[#12]

@JessyVision 

Link is correct, something is wrong with Microsoft servers or channels. Breaks even on root of support

If refresh from time to time it could appear.

Anyway, formula in general shall work.

You may see formula text instead of the result if Show formula is ON

Flash Fill is good enough, but please be careful with it. Sometimes it could give wrong results, especially on big data arrays.

[#13]

@JessyVision 

In cell D2 type:

=CONCAT(B2," ",C2)

[#14]

@port43 

As I understood actually the question was why in D2 is =A2&" "&B2, not the resulting text. In your case in the cell will be =CONCAT(B2," ",C2)