How can I add text to a formula in a cell. Example: The cell now is
=Sum(E38). How can I add to the beginning of the formula so that the
following words appear in the same cell The Number of Occurences Reported is
--
Marie

Hi Marie,

="The Number of Occurences Reported is "&SUM(E38)

or

="The Number of Occurences Reported is "&E38

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Marie" wrote in message ...
| How can I add text to a formula in a cell. Example: The cell now is
| =Sum(E38). How can I add to the beginning of the formula so that the
| following words appear in the same cell The Number of Occurences Reported is
| --
| Marie

="The Number of Occurences Reported is " & E38

You don't need SUM

"Marie" wrote:

How can I add text to a formula in a cell. Example: The cell now is
=Sum(E38). How can I add to the beginning of the formula so that the
following words appear in the same cell The Number of Occurences Reported is
--
Marie

On Jul 2, 1:32 pm, Toppers wrote:
="The Number of Occurences Reported is " & E38
You don't need SUM

That's what I also used to say. Then I realized that when E38
contains text, =SUM(E8) results in zero, whereas =E38 results in the
text. Likewise, =SUM(A1,A2) works, whereas =A1+A2 results in a #VALUE
error if A1 or A2 contains text. If the purpose is to always produce
a numeric result, there is a difference.

On the other hand, arguably =N(E38) is more to the point than
SUM(E38). And I see no value in some of the more debatable uses of
SUM(), e.g. SUM(A1+A2).

Marie, you might want to consider the following:

="The number of occurrences is " & text(n(E38), "0")

if you want to control the format of E38 in the formula. "#" would
result in a blank if E38 is zero or text; "0" ensures at least a
zero. On the other hand, no such format control is necessary if you
are sure that E38 will be an integer (or text).

You could also just use a formula like:

=E38
but give that cell a custom format
Format|cells|Number tab|Custom category:
"The Number of Occurences Reported is "0
Or if you like commas:
"The Number of Occurences Reported is "#,##0

By using the custom format, the value in the cell will still be a number. And
you can do more calculations with it if you want.





Marie wrote:

How can I add text to a formula in a cell. Example: The cell now is
=Sum(E38). How can I add to the beginning of the formula so that the
following words appear in the same cell The Number of Occurences Reported is
--
Marie

--

Dave Peterson

1. To add text to a formula in a cell, use the "&" symbol to concatenate the text with the formula.
2. Click on the cell where you have the formula =Sum(E38).
3. Click on the formula bar at the top of the screen.
4. Move the cursor to the beginning of the formula.
5. Type the following text: "The Number of Occurrences Reported is -- ".
6. Add the "&" symbol after the text.
7. Type the original formula after the "&" symbol, like this:
   Code:

   &=Sum(E38)

   .
8. Press Enter to save the formula.

The final formula should look like this: = "The Number of Occurrences Reported is -- "&Sum(E38).

This will display the text "The Number of Occurrences Reported is -- " followed by the result of the formula in the same cell.