Formula text showing instead of result...

[Yann St-Germain]

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.

 

[Ken Wright]

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

 

[Yann St-Germain]

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

 

[Yann St-Germain]

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...

 

[Ken Wright]

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_UNDERSCORE_AND_UPPER_CASE_main@
_REMOVE_UNDERSCORE_AND_UPPER_CASE_cana_REMOVE_UNDERSCORE_AND_UPPER_CASE_c.c_REMO
VE_UNDERSCORE_AND_UPPER_CASE_om> wrote in message

 

[Gord Dibben]

Yann

If the formulas will show results after you F2>Enter 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 Edit>Replace.

What: =
With: =

Replace All

Gord Dibben XL2002

 

[Yann St-Germain]

PERFECT!!!

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

Thanks for your help Gord!!

Yann

 

[Gord Dibben]

Yann

You just made my day.

Thanks for the feedback.

Gord