So far in this course we have learned to merge cells in excel, we have learned to combine cells in excel - using this knowledge we learned how to combine first and last names in excel, also we saw how to combine excel cells with line breaks where proper formatting is required. When it comes to formatting we cannot limit ourselves to line breaks - there are a lot of other scenarios that we come across on daily basis where we need to preserve the formatting of the cells while combining them. In this tutorial we'll discuss these scenarios and the differnet ways to overcome the problem.
Formatting values while combining cells in Excel :
We can simply combine cells in excel using a separator. Using this simple method we can combine cells with text values and also cells with text and number values. This is quite simple but the problem arises when the number values in question have some formatting. In this tutorial we’ll learn how to combine cells and preserve the formatting of the cells. To achieve this we make use of TEXT function of Excel. By using the TEXT function we can display the numbers as text and in the desired format.
Some examples where we can use the TEXT function before combining –
Formatting Currency signs – TEXT (A1, “$ ###,0.0”)
Formatting Dates – TEXT(A1,”dd mmm yyy”)
Suppose we need to combine text from column A, B and C and display the combined text in column E.
Construct the formula. – A1& TEXT(B1,”dd mmm yyy”)&C1
Enter the formula in the result cell.
Copy and paste the formula in all the cells of the column.
Note # :
We can also use the Concatenate function in place of the ampersand (&). In case of the concatenate function the formula that we’ll construct will be -
In our day to day Excel tasks we come across different types of formatting almost every day, hence it is very important to be able to format or preserve formatting of numbers while doing simple tasks like combining cells. In the above tutorial we learned the formatting of two very important and most frequently used scenarios - currency signs and dates. We also saw that we can achieve the same results by either using the ampersand operator or by using te concatenate function.