Are you enter the same section of data in different columns? Want to combine those columns in Excel? You can easily combine columns in excel by merging cells. That means you can easily merge cells without losing data. When you use the “Merge&Center” option from the context menu, you will lose data. When you want to combine columns in excel without losing data you need to use formula.
In this tutorial, I am going to show you two methods for combining columns in Excel. And I have provided a video tutorial for people who don’t want to spend much time and asking for reality.
- You May Like : How to split column in excel into multiple columns
- How to Insert Formula In Excel and Apply to Entire Column or Row
- 2 Simple Methods to Insert Excel into Word document
How to Combine columns in Office 365 Excel?
In this tutorial, we are going to see how to combine columns in an Excel sheet. We have mainly two types of excel sheets now a days. One is Office 365 Excel and the other is MS Excel. In this section, first, we will see how to combine columns in Excel. Follow the below steps carefully.
Total Time: 4 minutes
Step 1: Open Excel sheet
Firstly, open the excel sheet in Office 365 in which you want to combine columns. Enter the data in different columns which you want to combine in another column.
Step 2: Enter Formula
Now you will see the data in different columns in which you have entered it. But we need all the data in different cells in a single cell. That means we need to combine the data. So we will search methods for combining those cells. Firstly we will use one formula for combining cells.
The formula that we use here is =A2&” “&B2&” “&C2. Place the cursor in the cell in which you want to get combined cell result. Now enter =A2&” “&B2&” “&C2 in the column beside fx as shown in the above image.
Step 3: Combined columns
Once you entered formula in the specific cell and then click Enter. Then the data in cells will be combined. And that result will appear in the cell where you have placed the cursor.
* Type =A2&” “&B2&” “&C2 for separating words with a space.
* Type =A2&”,”&B2&”,”&C2 for separating words with a comma.
* Type =A2&”.”&B2&”.”&C2 for separating words with a dot symbol.
This is the one method procedure for combining cells in an Excel sheet.
- Office 365
- Office 365
In the previous section, we have seen one method for combining columns. In this section we will see another method. In this method, we don’t use Ampersand (&) symbol as formula. Follow the below steps.
- The first thing you need to is open Excel sheet in Office 365. In that, you can see the data in different columns or cells. Now place the cursor in the column where you want to see combined result.
- Now go to fx row in the upper screen. Enter =CONCATENATE(A2,B2,C2) just beside the fx row. Now press Enter.
- Once you press enter then the data in different column cells will merge and the result will appear as shown in the above image.
- You can see some spaces and commas for separating those cells results.
- If you type =CONCATENATE(A2,B2,C2) then cell data will merge without any spaces.
- If you type =CONCATENATE(A2,”,”,B2,”,”,C2) then cell data will separated by commas.
- If you type =CONCATENATE(A2,”.”,B2,”.”,C2) then cell data will be separated by a dot symbol.
- This is the second method for combining columns in an Excel sheet.
How to combine columns in MS Excel?
Here I am showing you two simple methods to Combine columns in excel by merging cells without losing data. You can use any one of the methods.
We are using the important formula to do this task. Make sure to watch a video, so that there would be no chance of mistake. However, we also have mentioned step by step instructions for you just after the video.
Combine columns in excel without space
Open your Excel sheet. Just see the below excel sheet. It has three columns (A, B, C). I am going to combine these three columns into one column (Column ‘E’).
- In the above sheet real data starts from 2nd row (A2, B2, C2). You need to merge A2, B2 & C2 cells.
- Click on the E2 cell. Type =A2&B2&C2 shows that in the above screenshot. (If your data starts from 1st row, you need to replace ‘2’ with ‘1’)
- Press “Enter”. You will see results in the E2 cell.
- Copy E2 cell and paste into the remaining cells. Or click at the right bottom of the E2 cell, hold the left click of a mouse, and drag to select the entire column. Immediately you will see the result in the entire column shows that in the below screenshot.
From the above picture, You can see that there is no space or any character between three words.
Combine columns in excel with space or special character
You can separate these three words with space or comma or dot or any other character.
- Use formula =A2&” “&B2&” “&C2 to separate words with space(there is a space between two colons and you can replace 2 with your corresponding row number).
- Use formula =A2&”,”&B2&”,”&C2 to separate words with comma.
- Use formula =A2&”.”&B2&”.”&C2 to separate words with a dot.
Likewise you can use any character between two colons to separate words.
You can use another formula to merge cells in excel.
Open excel sheet. Select E2 cell.
- Type =CONCATENATE(A2,B2,C2) to merge cells without space.
- Type =CONCATENATE(A2,”,”,B2,”,”,C2) to separate words with comma.
- Type =CONCATENATE(A2,”.”,B2,”.”,C2) to separate words with dot.
Press “Enter”. You will see result shows that in below screenshot. Copy same formula to entire column.
Thanks for reading my article about how to combine columns in excel. If you like it, do me favor by sharing it with your friends. You can also see the same procedure from Microsoft support topic. You can also follow whatvwant on Facebook and Twitter. Subscribe to whatVwant channel on YouTube for regular updates.