Formula text showing instead of result...

Hi,

I'm using Excel 2000 (9.0.6926 SP-3) and I have the following problem:

I have the following formula in the cell B2:
=SUBSTITUTE(A2, "blabla", "")

Instead of showing the result of the formula I get the formula's text just
like if it was between double quotes... If I click on the "=" sign on the
top left to edit the formula, the formula shows up with the different
parameters filled in and the good result shows up on the bottom in the
dialog box.

I couldn't find anything in the newsgroups on this..

Is there something I'm doing wrong here? Any work around?

Thanks in advance!!

==========================
Yann St-Germain
Programmer - Analyst / Webmaster
CANAC Inc.

Try formatting the cell as General first. Failing that, are you sure you
haven't set the sheet to display formulas - Tools / Options / View Tab / Uncheck
Formulas

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



"Yann St-Germain"
_REMOVE_BETWEEN_UNDERSCORES_ystger_REMOVE_BETWEEN _UNDERSCORES_main@_REMOVE_BETW
EEN_UNDERSCORES_canac._REMOVE_BETWEEN_UNDERSCORES_ com wrote in message
...
Hi,

I'm using Excel 2000 (9.0.6926 SP-3) and I have the following problem:

I have the following formula in the cell B2:
=SUBSTITUTE(A2, "blabla", "")

Instead of showing the result of the formula I get the formula's text just
like if it was between double quotes... If I click on the "=" sign on the
top left to edit the formula, the formula shows up with the different
parameters filled in and the good result shows up on the bottom in the
dialog box.

I couldn't find anything in the newsgroups on this..

Is there something I'm doing wrong here? Any work around?

Thanks in advance!!

==========================
Yann St-Germain
Programmer - Analyst / Webmaster
CANAC Inc.




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.537 / Virus Database: 332 - Release Date: 06/11/2003

Thanks for your response, but it did not work:

1- Formating to general didn't change anything.
2- The "Show Formulas" option in Tools / Options / View Tab was already
"unchecked".

I have other formulas that work ok in the same spreadsheet... I also have a
query (on another sheet of this workbook) that queries an Access 2000
database.

But, all of the new formulas that I insert don't show up properly... I tried
on 2 different machine here and it does the same.

I have 3 sheets:
1- CLEAN_DATA which contains "references" to certain fields on the 3rd sheet
and the 2nd sheet.
2- TRANSFORMED_DATA which contains "references" to certain fields on the 3rd
sheet.
3- RAW_DATA which contains the query

Anyone else had a simular problem??

I'm testing and if I find something, I'll let you all know.

Thanks again.

Yann



Try formatting the cell as General first. Failing that, are you sure you
haven't set the sheet to display formulas - Tools / Options / View Tab /
Uncheck
Formulas

After playing around (creating a new workbook and copy/pasting some stuff
from my original one), I found out that I get to a certain point where
creating simple formulas like:
=A2+1
doesn't work anymore...

I have absolutelly no idea why this happens...

If I find anything then I'll post again...

As long as the format of the cells is set to pretty much anything expect text,
and you don't have it set to display formulas, then you should be able to enter
a formula and have it calculate. Just changing the format of a cell with an
existing formula in it that is being treated as text will not make it work on
it's own. You also need to hit F2 on that cell and then hit enter for it to be
treated as a formula.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



"Yann St-Germain"
_REMOVE_UNDERSCORE_AND_UPPER_CASE_ystger_REMOVE_U NDERSCORE_AND_UPPER_CASE_main@
_REMOVE_UNDERSCORE_AND_UPPER_CASE_cana_REMOVE_UNDE RSCORE_AND_UPPER_CASE_c.c_REMO
VE_UNDERSCORE_AND_UPPER_CASE_om wrote in message
...
After playing around (creating a new workbook and copy/pasting some stuff
from my original one), I found out that I get to a certain point where
creating simple formulas like:
=A2+1
doesn't work anymore...

I have absolutelly no idea why this happens...

If I find anything then I'll post again...




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.538 / Virus Database: 333 - Release Date: 10/11/2003

Yann

If the formulas will show results after you F2Enter this usually means the
cells were originally formatted as "Text" before the formulas were entered.

A quick fix that works often is to select the cells then EditReplace.

What: =
With: =

Replace All

Gord Dibben XL2002


On Wed, 12 Nov 2003 16:35:35 -0500, "Yann St-Germain"
_REMOVE_UNDERSCORE_AND_UPPER_CASE_ystger_REMOVE_U NDERSCORE_AND_UPPER_CASE_main@_REMOVE_UNDERSCORE_A ND_UPPER_CASE_cana_REMOVE_UNDERSCORE_AND_UPPER_CAS E_c.c_REMOVE_UNDERSCORE_AND_UPPER_CASE_om
wrote:

After playing around (creating a new workbook and copy/pasting some stuff
from my original one), I found out that I get to a certain point where
creating simple formulas like:
=A2+1
doesn't work anymore...

I have absolutelly no idea why this happens...

If I find anything then I'll post again...

PERFECT!!!

Doing a Replace All "=" by "=" worked!!!

Thanks for your help Gord!!

Yann

Yann

You just made my day.

Thanks for the feedback.

Gord

On Thu, 13 Nov 2003 10:28:02 -0500, "Yann St-Germain"
_REMOVE_BETWEEN_UNDERSCORES_ystger_REMOVE_BETWEEN _UNDERSCORES_main@_REMOVE_BETWEEN_UNDERSCORES_cana c._REMOVE_BETWEEN_UNDERSCORES_com
wrote:

PERFECT!!!

Doing a Replace All "=" by "=" worked!!!

Thanks for your help Gord!!

Yann