Forum Discussion
Combine data from 2 columns into 1 column
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 (&)
Select the cell where you want to put the combined data.
Type = and select the first cell you want to combine.
Type & and use quotation marks with a space enclosed.
Select the next cell you want to combine and press enter. An example formula might be =A2&" "&B2.
Doesn't work. Example?radfordsix
- 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
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.
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).
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]
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.
- On the first worksheet select the cells/column that contains the combined data you want copied to the second worksheet
- 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.
- Thanks! I had the same issue as Ben above. Paste special > Paste Link was the perfect fix!!
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
