Excel In Your Office: Combine Text and Formula In a Cell

Tuesday, March 18, 2014

Combine Text and Formula In a Cell

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.