Utilizing multiple columns within Excel can save you the hassle of manually joining the columns manually. Learn how to join two columns using Excel.
When you’re working with Excel and you have data spread over multiple columns that wish to merge it isn’t necessary to perform this manually. You can instead make use of an easy and simple formula to join columns.
We’ll demonstrate how you can combine the columns of two columns within Excel with the ampersand symbol or CONCAT function. We’ll also provide some suggestions for formatting the data to ensure it is exactly the way you’d like it to.
How to Combine Columns in Excel
There are two ways to connect columns within Excel: the ampersand symbol and the concatenate formula. In most instances, using the ampersand method is faster and simpler than using Concatenate Formula. But, choose the one you are at ease with.
1. How to Combine Excel Columns With the Ampersand Symbol
- Click on the cell that you would like the data to be placed.
- Type =
- Select the first cell that you’d like to mix.
- Type &
- Select the second cell that you wish to join.
- Enter to enter. Enter key.
For instance, if you were to mix B2 and A2 cells The formula could look like: =A2&B2
2. How to Combine Excel Columns With the CONCAT Function
- Select the cell in which you’d like the combined data to be placed.
- Type =CONCAT(
- Click on the first cell that you’d like to mix.
- Type ,
- Select the second cell that you’d like to join.
- Type )
- Enter on the Enter key.
For instance, if you would like to combine cells A2 and B2 The formula could include: =CONCAT(A2,B2)
This formula was originally CONCATENATE rather than CONCAT. Utilizing the former will join two columns within Excel however it is decreasing, which is why it is better to use CONCAT in order to make sure that it is compatible with both current and the future Excel versions.
How to Combine More Than Two Excel Cells
You are able to combine the number of cells you’d like with either method. You can simply repeat the formatting as follows:
- =A2&B2&C2&D2 … etc.
- =CONCAT(A2,B2,C2,D2) … etc.
How to Combine the Entire Excel Column
Once you’ve placed this formula inside one cell, then you can then use this formula to populate the remainder of the column. There is no need to input the cell names you’d like to merge.
To accomplish it, double-click the bottom-right corner of the cell that is filled. Or, left-click and drag the bottom-right corner of the cell you want to fill across the column. This is an Excel AutoFill trick that helps you create spreadsheets more quickly.
Tips on How to Format Combined Columns in Excel
The group of Excel columns may include text, numbers dates, text, and so on. This is why it’s not always appropriate to just leave the cells merged without formatting the cells.
To aid you Here are some suggestions on how to format the cells of a group. The examples we’ll use will be referring to the ampersand formula however the principle is similar to using the formula CONCAT.
1. How to Put a Space Between Combined Cells
If you were to have two columns, a “First name” column and an “Last name” column, you’d need an empty space between them.
To accomplish this to do this, the formula is: =A2&” “&B2
This formula suggests adding B2’s contents then , add a space then include the contents of B2.
It doesn’t need to be an empty space. You can place anything you like between speech marks, for example, an apostrophe, a dash and any symbol, or word.
2. How to Add Additional Text Within Combined Cells
The cells that are combined don’t need to include the original text. You are able to add any additional information you’d like to add.
Let’s suppose that cell A2 has the name of someone (e.g., Marge Simpson) and cell B2 has the person’s age (e.g. 36, for example.). This can be incorporated into a sentence which is “The character Marge Simpson is 36 years old”.
To accomplish this then, the formula is: =”The character “&A2and” is “&B2and” years old”
The text in the additional paragraph will be wrapped with speech marks, and then followed by an and. It is not necessary to make use of speech marks when referring to cells. Be sure to specify where spaces should go, for instance “The character ” with a space at the end.
3. How to Correctly Display Numbers in Combined Cells
If the original cells have numerals that are formatted, like currency or dates you’ll see that the cell combined eliminates the formatting.
It is possible to solve this issue using the TEXT function that you can utilize to specify the format you require.
Let’s say that cell A2 has the name of someone (e.g., Marge Simpson) Cell B2 holds their birth date (e.g. 01/02/1980).
For combining these two, you could decide to make use of these formulas: =A2&” was born on “&B2
But, it will show: Marge Simpson was born on 29252. This is because Excel converts the properly formatted date of birth to the form of a simple number.
Utilizing the TEXT feature You can inform Excel what format you’d like the merged cell formatted. Like so: =A2&” was born on “&TEXT(B2,”dd/mm/yyyy”)
This formula is a little more complex than other formulas So let’s dissect it:
- = A2 – join cell A2.
- and” was born on “” – include the word “was born on” with an asterisk across both sides.
- and TEXT Add something using Text function.
- (B2,”dd/mm/yyyy”) Combine cell B2 and then apply the format of dd/mm/yyyyyyyyyyyyy the field.
You can change the format to suit whatever number you need. For instance, $#,##0.00 will show the currency using the separator of a thousand and two decimals #?/? will convert an decimal to a fraction the format H:MM AM/PM would display the time and it goes on.