We know how to enter values in
cells. We also know how to enter formulas to play with numbers. But what if I have
to combine text and formulas in one cell? For example: I want to show “Total
bill value is 1000” where in 1000 comes from summing of cell A1:A10. So
essentially we have to combine some text and a formula. Let’s see how to do it.
We will use Excel’s concatenation
operator “&” and quotation marks to achieve this. In functions, text can be
entered between two quotation marks. In the cell where you want the result,
type the formula.
="Total bill value is " & SUM(A1:A10)
Easy? Yes it is.
I have another situation, I want
to display “Today is DD:MMM:YYYY” wherein DD:MMM:YYYY is the date returned by
Excel built-in function TODAY. How do I do it? I enter the below formula in any
cell I want the result in.
="Today is " & TODAY()
That’s it? Actually no, if you
tried entering this formula result will be something like this (on the day I am
writing this post): Today is 41716. I wanted date but what is 41716? Actually
this number is the date but since Excel considers entire cell to have text, you
cannot format this number to date using Excel’s formatting options. You will
have to do it in the function itself using another Excel built-in function TEXT. Let’s
see the modified formula.
="Today is " & TEXT(TODAY(),"DD-MMM-YYYY")
This show up a result: Today is 18-Mar-2014. Just what I wanted!
