Excel - The CONCATENATE function to add text etc. to values in a cell

In an earlier post (http://computertipsforbeginners.blogspot.com/2011/10/starting-with-formulae-in-excel-sum.html) I had started off with the simple SUM function. Now  for a function which is not so frequently used, but which I like. When you just give the sum total, it not only looks bland,  it is also meaningless unless there is say “Total” typed in the adjacent cell. So why not get the word total, or some other explanatory words you like, in the same cell? It is really quite easy. Let’s start with a simple line in a simple exercise. Say you have a group of six students who have spent for eats and beverages. Enter it in Excel in this form starting from cell A1:

Name of student	Eats	Beverages
Asha	7	9
Beni	8	8
Chandra	7	7
Deepak	4	5
Esha	10	10
Farida	9	10
Govind	10	9

 Now suppose you want to give the amount spent in Eats by the group. The formula =SUM(B2:B8) gives the total amount of 55 spent on Eats. To get the sentence My students spent 55 on Eats in cell A9, type =CONCATENATE("My students spent ", SUM(B2:B8), " on ", B1).

The following are to be remembered:

1.       Two functions have been simultaneously used here. As in any other formula, the number of opening brackets must equal the number of closing brackets. In fact a set of brackets is used per function.

2.       The text that is required is typed within inverted commas, exactly as is needed – spelling, Capitals etc. are to taken care of.

3.       To get space before and after 55, the space forms part of the text within the inverted commas.

4.       Note the commas between each set i.e. between the text My students spent and SUM(B2:B8), then between SUM(B2:B8) and  on etc.

5.       B1 refers to the contents or value of the cell B1 hence does not appear within inverted commas. In place of " on ", B1  a " on Eats" can be used. This is simpler but is not suggested as changes become difficult. Thus in case you subsequently find that the expenses were not on Eats and Beverages, but on Lunch and Dinner just change the value (contents) in cells B1 and B2 and the formula will automatically adjust the contents of cell  A9.

The Concatenate function joins text, numbers, cell values, or a combination of these. Going further, this function will be found really useful as familiarity with formulae increases.