I recently had to figure out how to combine the data from two different columns that I needed in one column for a project at my workplace. After some searching, I discovered the ampersand (&) operator. I was surprised just how similar Excel is to web programming.
I had a few hurdles: I needed to combine two separate columns of data into one cohesive column. I also needed to figure out how to copy and paste the new data into another file while leaving behind the formulas that I used to get those columns combined in the first place.
Here is how I did it. Hopefully this can help someone else out there:
- Insert a new column next to the two columns that you will combine
- identify the first and second cells to combine (ie. A2 and A3)
- create a new function in the topmost new cell: “
= A2 & " " & A3”- the ampersand (
&) combines the two values on either side of the operator - the
" "outputs a blank space - the string combined outputs this: the value of A2, plus a blank space, plus A3
- the ampersand (
- Click the little checkbox next to the formula to tell Excel you are finished writing the formula
- With that formula cell still selected, click and drag the little black square in the bottom right of the cell down to the bottom of your new column, stopping at your last data entry
- This will copy and reformat the formula you created prior for each line
- This will auto-populate the combined data in the new column
- click out on an empty cell
- double-check that everything is being outputted correctly
- select the entire column of the new combined data from the formulas
- press the copy command (Ctrl + C for windows)
- click on the single topleft destination cell to select it
- in the Home Tab, within the Clipboard group, click the down arrow below “paste”
- Under the “Paste Values” heading, click on the “Values” tile.
- click out on an empty cell
- Your data should now be completely pasted into the destination column