In Microsoft Excel, how do you combine two columns? (Quick and Easy Method)

If you’re using Excel and want to merge data from different columns, you don’t have to do it manually. Instead, you may merge columns using a simple formula.

We’ll teach you how to use the ampersand symbol or the CONCAT function in Excel to combine two or more columns. We’ll also give you some pointers on how to style the data so that it appears just the way you want it to.

How to Combine Columns in Excel

In Excel, there are two ways to merge columns: the ampersand symbol and the concatenate formula. Using the ampersand approach is often faster and simpler than using the concatenate formula. However, utilize whatever method makes you the most comfortable.

1. How to Combine Excel Columns With the Ampersand Symbol

1. Select the cell where you wish the combined data to be placed.
2. Type =
3. Select the first cell to merge.
4. Type &
5. Select the second cell to merge.
6. Press the Enter key.

If you wanted to combine cells A2 and B2, for example, the formula would be: =A2&B2.

2. How to Combine Excel Columns With the CONCAT Function

1. Select the cell where you wish the combined data to be shown.
2. Type =CONCAT(
3. Select the first cell to merge.
4. Type ,
5. Select the second cell to merge.
6. Type )
7. Press the Enter key.

If you wanted to combine cells A2 and B2, for example, the formula would be: =CONCAT (A2,B2)

This formula was formerly CONCATENATE rather than CONCAT. To merge two columns in Excel, use the former, but it is depreciating, therefore use the latter to guarantee compatibility with current and future Excel versions.

How to Combine More Than Two Excel Cells

Using either strategy, you can combine as many cells as you wish. Simply repeat the formatting as follows:

• =A2&B2&C2&D2 … etc.
• =CONCAT(A2,B2,C2,D2)… and so on.

How to Combine the Entire Excel Column

Once you’ve entered the formula in one cell, you may use it to fill the remainder of the column automatically. You don’t have to manually enter the names of the cells you wish to merge.

Double-click the bottom-right corner of the full cell to do so. Alternatively, left-click and drag the filled cell’s bottom-right corner along the column. It’s an Excel AutoFill method for speeding up spreadsheet creation.

Tips on How to Format Combined Columns in Excel

Your merged Excel columns might include text, numbers, dates, and other data. As a result, leaving the cells together without formatting isn’t usually a good idea.

Here are some pointers on how to format joined cells to assist you. We’ll use the ampersand approach in our examples, although the principle is the same for the CONCAT formula.

1. How to Put a Space Between Combined Cells

You’d require a gap between the two cells if you had a “First name” column and a “Last name” column.

To do this, the formula would be: =A2&” “&B2

This formula instructs you to add the contents of A2, then a space, and finally the contents of B2.

There is no need for a gap. You may insert anything between the speech marks, such as a comma, a dash, or any other symbol or text. The joined cells do not have to include just their original text. You are free to provide any extra information you choose.

2. How to Add Additional Text Within Combined Cells

Assume cell A2 includes a person’s name (for example, Marge Simpson) and cell B2 provides their age (e.g., 36). This may be turned into a statement that says, “The character Marge Simpson is 36 years old.”

To do this, use the following formula: =”The character “&A2&” is “&B2&” years old”

The extra content is enclosed in speech marks and followed by a &. When referring to a cell, you do not need to use speech marks. Remember to include the spaces; for example, “The character” with a space at the conclusion.

If your original cells include structured numbers, such as dates or currencies, the merged cell will remove the formatting.

3. How to Correctly Display Numbers in Combined Cells

You may address this by using the TEXT function to specify the needed format.

Assume cell A2 includes someone’s name (for example, Marge Simpson) and cell B2 provides their date of birth (for example, 01/02/1980).

You might use the following formula to merge them: “&B2” gave birth to “=A2&.”

However, this will result in: Marge Simpson was born in the year 29252. This is because Excel turns a properly formed date of birth into a simple integer.

You may tell Excel how you want the combined cell to be styled by using the TEXT function. As an example: “&TEXT(B2,”dd/mm/yyyy”) was born on “&A2&”

This one is a little more difficult than the others, so let’s break it down:

You may change the format to match whatever the number demands. $#,##0.00, for example, would display money with a thousand separator and two decimals, #?/? would convert a decimal to a fraction, H:MM AM/PM would display the time, and so on.

• =A2 – merge cell A2.
• \s & “was born on ” – add the words “was born on” on both sides with a space.
• &TEXT – use the text function to add anything.
• (B2,”dd/mm/yyyy”) – combine cells B2 and apply the dd/mm/yyyy format to the contents of that field.

More samples and information may be found on the Microsoft Office TEXT function support page.

When you click a cell in the combined column, you’ll see that it still has the formula (e.g., =A2&” “&B2) rather than plain text (e.g., Marge Simpson).

How to Remove the Formula From Combined Columns

This isn’t always a negative thing. It implies that anytime the original cells (for example, A2 and B2) are changed, the combined cell will also be updated to reflect those changes.

It does, however, imply that deleting the original cells or columns would break your merged cells. As a result, you should delete the formula from the combined column and replace it with plain text.

To do so, right-click the combined column’s heading to highlight it, then choose Copy.

Next, right-click the combined column’s header again and pick Values from the Paste Options menu. Now that the formula has been removed, you are left with plain text fields that you may alter directly. You may combine columns instead of combining them in Excel. This will combine many horizontal cells into a single cell. When you merge cells, you only preserve the values from the upper-left cell and delete the remainder.

To do so, choose the cells or columns to be merged. On the Home tab of the Ribbon, click the Merge & Center button (or use the dropdown arrow next to it).

How to Merge Columns in Excel

Read our article on how to combine and unmerge cells in Excel for more details. You may even combine complete Excel sheets and files.

You now understand how to join columns in Excel. You may save a lot of time by not having to integrate them by hand. It’s just one of many ways you may utilize formulae to speed up routine Excel chores.

For more information on this, read our article on how to merge and unmerge cells in Excel. You can also merge entire Excel sheets and files together.

Save Time When Using Excel

Now you know how to combine columns in Excel. You can save yourself lots of time—you don’t need to combine them by hand. It’s just one of the many ways that you can use formulas to speed up common tasks in Excel.

You are looking for information, articles, knowledge about the topic In Microsoft Excel, how do you combine two columns? (Quick and Easy Method) on internet, you do not find the information you need! Here are the best content compiled and compiled by the appsladder.com team, along with other related topics such as: How To.

Related videos about In Microsoft Excel, how do you combine two columns? (Quick and Easy Method)

[‘‘]