Excel is heavily dependant on formulas, and it seems like there is literally a formula for everything. For example, have you ever been working in Excel and needed to combine the contents of two different cells into a new one without adding them together? While you can copy and paste the content of the different cells you can also use the concatenate formula.
Using the concatenate formula to combine cells
If, for example, you have a spreadsheet with first names in column A, last names in column B, and want to combine them into column C to display the full name you can do so by:
- Clicking on cell C2 (or the row where the information you want to combine is)
- Typing =concatenate(
- Clicking on cell A2 and then adding a comma (,)
- Clicking on cell B2 and closing the formula with a closing bracket
- Hitting Enter
You should see the two cells are now combined in cell C2, with the formula for cell C2 reading:
=CONCATENATE(A2,B2).
The problem is, there will be no space inbetween the letters or numbers, so you will need to edit the formula to read:
=CONCATENATE(A2,” “,B2)
The double quotations with a space in between them tells Excel to add a space to the cell in between the contents of A2 and B2.
If you have more than two columns you would like to combine, then simply add a comma after each cell. If for example you have three columns (A1, B1, and C1) you would enter the formula:
=CONCATENATE(A1 ” “,B1 ” “,C1) in column D1.
Combining two cells without concatenate
While concatenate works well, there is actually a shortcut that you can use which involves the ampersand ‘&’:
- Click on cell C2 (or the row where the information you want to combine is)
- Type =
- Click on cell A2 and then type & in the formula.
- Click on B2 and hit Enter
You should see the contents of A2 and B2 combined together in C2. If you click on cell C2 and look at the formula, it should read: =A2&B2.
The only problem is, there won’t be a space between the content. To add a space, you can edit the formula so that it reads:
=A2&” “&B2
Note the space between the two quotation marks. This tells Excel to add a space between the contents of A2 and B2.
Once you have the base formula on one cell, you can press the small box at the bottom of the cell and drag it down the row so that the other information can be quickly compiled. This makes it much easier than having to copy and paste the content individually. And, If you would like to learn more Excel tips, contact us today. We can save you valuable time and resources.
Courtesy: techadvisory.org