Excel is a handy program with many built-in functions. There are many formulas that allow you to automate work in Excel. Sometimes users encounter problems, for example, when trying to combine 2 columns. This is where the ampersand symbol or the concatenate formula can help. If you use the formula correctly, the Excel program will be your best helper.
Content
What to do to combine columns in Excel
Before the program was released, people had to manually do the calculations on paper and make the necessary graphs, which was very time-consuming.
The original purpose of this program was for office work. Since only companies could afford a computer. However, over time, when computers began to appear in the homes of ordinary people – the number of users began to grow. Nowadays, almost every computer has a Microsoft Office package. It’s also popular with many families for family budget planning.
What you should know about Excel
Excel is a widespread program and it’s used to work with spreadsheets. It’s hard to imagine without it the work in accounting, analytics, economics, and in any field of activity where you need to make calculations.
Excel is part of Microsoft Office, which consists of a whole set of products that allows you to create different documents, spreadsheets, presentations, and much more. In addition to this Excel program, there is a similar number of similar programs that perform the same functions. Excel is definitely the most popular and powerful of them.
Despite the fact that the program is very handy for calculating and automating many processes, difficulties may arise, for example, in combining columns. If you encounter such a problem, don’t worry, there are ways to solve it.
How to combine columns in 2 ways
There are several ways to combine 2 columns.
- The ampersand symbol
- The concatenate formula
You can use any of them. It depends on which method you want to use. However, still in many cases, ampersands are faster and easier.
How to Combine Excel Columns With the Ampersand Symbol
- The first step is to click in the cell where you want to put the combined data
- Next, click [ = ]
- The third step is to click the first cell that you want to combine
- Then click [ & ]
- After that, you need to click the second cell that you want to combine
- And the final step is to click ENTER
That is, by combining cells A2 and B2, your formula should show up like this =A2&B2
How to Combine Excel Columns With the the concatenate formula
- The first step is to click on the cell where you want to put the combined data
- After that, type CONCAT
- The third step is to click the first cell that you want to combine
- Then click [ , ]
- After that, you need to click the second cell that you want to combine
- Then click [ ) ]0
- And the final step is to click ENTER
So your formula would look like this if you want to combine A2 and B2: =CONCAT(A2, B2)
How to combine more than two cells
All you have to do is add up the numbers of the cells you want to combine, using the same algorithm as in the methods described above. Here are examples of such formulas:
- =A2&B2&C2&D2
- =CONCAT(A2&B2&C2&D2)
How to combine an entire Excel column
If you need to combine an entire column, don’t waste time manually entering the name of each cell.
Once you put the formula in one cell, you can use it to automatically fill the rest of the column.
To do this, double-click in the lower right corner of the filled cell, or left-click and drag the bottom right corner of the filled cell down the column.
Tips on How to Format Combined Columns in Excel
Combined columns may contain different dates, numbers, text. In this case, it is inconvenient to leave them unformatted.
Let’s take the ampersand method as an example, but the logic of working with the CONCAT formula is the same.
How to add additional text to Combined cells
It should be noted that the information in a cell doesn’t have to consist only of the original text, any information can be added.
For example, cell A2 contains someone’s name (Martha), and- cell B2 contains their age (e.g. 8). To combine the information about a person’s name and age, use this formula: =”The character “&A2&” is “&B2&” years old”.
How to Put a Space Between Combined Cells
For example, in the column ‘First Name’ and ‘Last Name’ you have to put a space between the two cells to separate the text. Use the formula: =A2&””&B2.
How to remove a formula from joined columns
If you click on a cell, you will see that it contains a formula and not the original text. This doesn’t mean that something isn’t displayed correctly. It means that when you update the original cells (e.g., A2 and B2), the merged cell will automatically update to reflect those changes. However, note that if you delete the original columns or cells it will break the cells that were previously merged. So make the formula plain text to delete.
To do this, you need to right-click the merged column header to select it, and then click Copy. Then right-click the merged column header again – this time, under Paste Options, choose Values.
After these steps, your formula should disappear and you should be left with plain text cells that you can edit directly.
So, if you spend a little time reading and practicing these formulas in Excel. You will be able to spend much less of your effort and time combining cells in these ways.
