Certain functions for the control of data using Excel are simple, such as adding currencies. However, there are some which are not so, for example, the ability to combine columns. In this article, we’ll demonstrate methods to combine two columns using Microsoft Excel
There is a chance that you have transferred data from a different place or have somebody add data to your spreadsheet. If you have two columns which be more effective as a single column and you want to combine them, you could do so with an operator, or by a feature.
About Merging Columns in Excel
You may be aware that Excel offers the merge cell option. However, if you choose to use this feature, only the content in the initial (far-left) cell is saved. If you’re looking to join two columns but ensure that all data is preserved it isn’t an option you should make use of.
In order to hold on to the information contained within two columns, combine all the data in the third column. Prior to combining the information in cells, as explained below make sure you prepare them the third column, in the event that it is required.
Follow the next actions:
- Combine two cells one from each column in the row that starts.
- Transfer the formula to other rows in the column.
- Optional: Convert formulas into static values.
How to Combine Columns in Excel
There are two ways for combining columns within Excel: the ampersand symbol as well as concatenate formula.
For many situations, the ampersand symbol is more efficient and more user-friendly than using of the concatenate formula. But, choose the one you are most comfortable using.
1. How to Combine Excel Columns With the Ampersand Symbol
- Choose the cell where you would like the information to be placed.
- Type =
- Choose the first cell you’d like to mix.
- Type &
- Select the second cell that you’d like to merge.
- Hit to enter. Enter key.
If, for instance, you were to mix cells B2 and A2 The formula is: =A2&B2
2. How to Combine Excel Columns With the CONCAT Function
- Choose the cell where you’d like your combined information to be placed.
- Type =CONCAT(
- Choose the first cell you’d like to join.
- Type,
- Select the second cell that you’d like to merge.
- Type )
- Enter on the Enter key.
If, for instance, you want to merge B2 with cell A2 then the formula would be: =CONCAT(A2,B2)
The formula was previously CONCATENATE instead of CONCAT. Utilizing the former will connect two columns within Excel However, it’s declining, and you must make use of the second to ensure the compatibility of future and current Excel versions.
How to Combine More Than Two Excel Cells
You are able to mix the number of cells you’d like with either of these methods. Just repeat the formatting the following:
- =A2&B2&C2&D2 … etc.
- =CONCAT(A2,B2,C2,D2) … etc.
How to Combine the Entire Excel Column
After you’ve put the formula into one cell, you are able to make use of it to populate other columns. There is no need to input every cell’s name you wish to merge.
For it, double-click the bottom-right corner of the empty cell. Alternately, left-click and drag the lower-right corner of the cell that is filled across the column. This is an Excel AutoFill technique to create spreadsheets more quickly.
Tips on How to Format Combined Columns in Excel
The group of Excel columns might include text, numbers dates, and much more. Therefore, it’s not often advisable to keep the cells merged without formatting the cells.
For your convenience Here are some ways to format the combined cells. In the examples below we’ll use the ampersand technique, however, this logic applies to the formula CONCAT.
1. How to Put a Space Between Combined Cells
If you have a “First name” column and a “Last name” column, it would be best to leave to create a space between these two columns.
For that, the equation should include: =A2&” “&B2
The formula suggests adding B2’s contents and then adding a space after which you can add the contents from B2.
The word doesn’t need to be an empty space. It is possible to put anything you’d like between the speech marks like the comma or dash and any other symbol, or words.
2. How to Add Additional Text Within Combined Cells
The combined cells don’t require their initial text. It is possible to add any additional information you like.
We can imagine that cell A2 has names of people (e.g., Marge Simpson) and cell B2 holds the person’s age (e.g. 36). It is possible to build it into a phrase that says “The character Marge Simpson is 36 years old”.
In order to do that then, the formula is: =”The character “&A2and” is “&B2and” years old”
The extra text is enclosed in speech marks, and then the text is then followed by an and. There is no need for speech marks to refer to the cell. Make sure you include the location where spaces are to be placed; for example “The character ” with a space in the middle.
3. How to Correctly Display Numbers in Combined Cells
This can be solved using the TEXT function that is used to determine the format you require.
Cell A2 could contain names of people (e.g., Marge Simpson) and cell B2 has the birth date (e.g. 01/02/1980).
If you want to combine these two, it is possible of using this formula: =A2+” was birthed in “&B2
But, it will show: Marge Simpson was born on 29252. This is because Excel will convert the correct formatting date of birth into an uncomplicated number.
Utilizing the TEXT feature You can inform Excel how you’d like the cell you have merged to be structured. Like so: =A2&” was born on “&TEXT(B2,”dd/mm/yyyy”)
It’s a little more complicated than the other formulas we’ll break it down:
- = A2 Combine cells A2.
- and” was born on “ – include the word “was born on” with the space between them.
- + TEXT Add something using the function of the text.
- (B2, “dd/mm/yyyy”) Then, merge the B2 cell and then apply the format of dd/mm/yyyy to the field.
It is possible to change your format to suit whatever numbers require. In this case, $#,##0.00 could show currency, with the separator of a thousand as well as two decimals. #?/? could transform a decimal into a fraction The format of H: MM AM/PM will show the date, etc.
More details and examples about the Microsoft Office TEXT function help page.
How to Remove the Formula From Combined Columns
If you select any cell in the column that is combined and you’ll see that the cell still has that formula (e.g. = and” “&B2) instead of simply a line of text (e.g., Marge Simpson).
This isn’t necessarily a negative thing. This means that if the cells that were originally used (e.g. A2, A2, and B2) are changed and the cell that is combined will update automatically to reflect the modifications.
But, this does suggest that if you remove the columns or cells that were originally created, it could break your combination cells. In this case, you could decide to delete the formula from the column and simply change it to just plain in text.
To do this, click right-click the head of the column, to bring it into focus, and then press
Then, click the header of the column, but this time under the Paste Options choose values. This time, the formula has disappeared and you’re left with simple text cells that are editable directly.
What is the Reason for Merge Columns in Excel?
Understanding how columns can be combined will reduce the number of data entries that you have to complete and make it simpler to analyze and produce huge amounts of data. In the example above, you could have a listing of the first names within one column as well as the names of their respective last names in a different column. With the merging feature of the program, it is possible to combine data from both columns and put the results into a separate column with the full name of each individual. In addition to text strings, you may utilize this feature to join numbers as well as a combination of letters.