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.
- 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 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 important formula to do this task. Make sure to watch a video, so that there would be a 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 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 in 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.