Combine different cells without losing data

MS excel is the application that can solve your every problem in calculation but there is now any button to join/combine two or more cell into new one cell.

In this article we will learn how to show two or more values in one cell from different cells. Merging or combining different cell is common issue that most of the math or accounting users face in different stages. See below

In above picture, we have combined B1, B2, B3, B4 and B5 cell in one cell having biodata of a student like Name, Roll Number class and etc. In Red Cell the the cell having Formula to collect and show the values. Changing the value in one cell results automatically change in the value in combined cell. This is the topic we are going to discuss today,

There are two way to combine the cells;

1. Combining by CONCATENATE function
2. Combining by & Operator

Combining by CONCATENATE function

MS Excel has provided many methods to to this and CONCATENATE is one of them that is widely used in the word by most of the users.

Concatenate is the function that will help us to get our objective. This function merges the values of different cells into one new cell without losing actual data.

The Following is the formula to concatenate the values

• Combine different cell values without spaces between the values: =CONCATENATE(B1,B2,B3,B4,B5)

This value will combine the values in one cell but there will be no space or comma or any other symbol. See Below

This is not the perfect way to express any value without space or comma. Lets learn How to insert the comma between the values.

• Combine different cell values and separate the values with comma: =CONCATENATE(B1,",",B2,",",B3,",",B4,",",B5)

Here we add some more symbol in the formula bar. In above formula you can see there is addition of "," in the formula =CONCATENATE(B1,",",B2,",",B3,",",B4,",",B5)

All the values separated by Comma is much better than other,  but still is we insert space formula after comma than it look more attractive and official like this (Zahid, 341, MBA, GCU, Fsd), lets learn how to do this.

• Combine different cell values into one cell separated by spaces =CONCATENATE(B1,", ",B2,", ",B3,", ",B4,", ",B5)

In last formula we have inserted "," in bar to insert comma. but now we will insert  ",  " (Space after comma) after every value =CONCATENATE(B1,", ",B2,", ",B3,", ",B4,", ",B5).

The Following is the actual formula inserted in worksheet

In above picture you can see the formula separated by somma and space with black color and values with different colour. After getting the correct formula and pressing enter you will get the following result

Yes and Done, this is the perfect and official format used every where like (Zahid, 341, MBA, GCU, Fsd)

There is another way to combine or merge the values of different cells into one cell without losing data. That was is to use & Operator in formula bar.

Click Here to learn Combining by & Operator