Formulas showing up as text

F

Frank Costa

I have read previous bulletins about this where after entering a formula
(starting with an "=" sign, it shows up as text versus the result. I have
done the following:
1) Checked to see if the Tools>Options>View box is unchecked.
2) Verified the format to make sure it is listed as a number (though it
should still be allowed to be text).
3) Taken a different cell, entered "=1+2" (of course, without quotes) to
make sure that the result is "3" and copied it into the cell i'm having an
issue with (and it gives me the result of "3"), I have then formated it to
text and still get the "3" result. But then, when I make a change to put in
the formula I need, it just shows up as text, starting with an = sign.
4) I've tried to copy and paste the whole worksheet into a new worksheet (so
that the defaults are there) but that doesn't work.

I have found a work around, which is to do a 'replace' when the cell gives
me the formula, but this is a huge pain in the neck and is taking me way too
long. It is also giving me very little confidence that the error won't
appear at some time in the future.

I have also found that I can take some formulas that are requesting a
blank/" " to show up in the cell (versus a 0) so that it doesn't get counted
with my COUNTA formula, but in some cells it shows as a blank, and others as
a zero 0. Again, with me verifying the format of the cell and even cutting
and pasting from one that works.

PLEASE HELP ME! THANKS IN ADVANCE
 
N

Niek Otten

< I have then formatted it to text >

That's the problem. The cell should be formatted as General (or Number) before you enter the formula

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have read previous bulletins about this where after entering a formula
| (starting with an "=" sign, it shows up as text versus the result. I have
| done the following:
| 1) Checked to see if the Tools>Options>View box is unchecked.
| 2) Verified the format to make sure it is listed as a number (though it
| should still be allowed to be text).
| 3) Taken a different cell, entered "=1+2" (of course, without quotes) to
| make sure that the result is "3" and copied it into the cell i'm having an
| issue with (and it gives me the result of "3"), I have then formated it to
| text and still get the "3" result. But then, when I make a change to put in
| the formula I need, it just shows up as text, starting with an = sign.
| 4) I've tried to copy and paste the whole worksheet into a new worksheet (so
| that the defaults are there) but that doesn't work.
|
| I have found a work around, which is to do a 'replace' when the cell gives
| me the formula, but this is a huge pain in the neck and is taking me way too
| long. It is also giving me very little confidence that the error won't
| appear at some time in the future.
|
| I have also found that I can take some formulas that are requesting a
| blank/" " to show up in the cell (versus a 0) so that it doesn't get counted
| with my COUNTA formula, but in some cells it shows as a blank, and others as
| a zero 0. Again, with me verifying the format of the cell and even cutting
| and pasting from one that works.
|
| PLEASE HELP ME! THANKS IN ADVANCE
 
S

SteveW

Text is the format *not* the content
changeing the cell format to Text doesn't convert the formula or value to
Text
But when entering data the format is used to determine the actual content
So if you have lots of cells that were text format, when you entered the
formula

The trick is
Select the text cells and format them as number
Now put 0 in a spare Number cell, select and copy it
Select the cells to convert, Paste Special... choose Add


Steve
 
G

Gord Dibben

Frank

Just changing the formatting from Text to General or Number is not enough.

You must now coerce Excel into changing.

F2 and ENTER on each cell.

If you have a great whack of these you can do an Edit/Replace on the range.

Replace what: =

Replace with: =

Repace all.


Gord Dibben MS Excel MVP
 
Top