Formulas showing in cell???

M

Miket6086

I keep getting a formula showing in the cell after I edit i
(occasionally). Show formulas is turned off and edit in cell is on.

How can I make the formula go awaw and simply show the results whic
when edited the results shows correctly?
 
F

Frank Kabel

Hi
probably the cell is formated as 'Text' change the cell format to
'General' and re-enter your formula
 
F

firefytr

along with Frank's suggestion, which i'm in total agreement with
sometimes i've run into the fact that even changing the cell back t
it's native cell format will not always solve your problem. if this i
the case, you may want to think about putting it into another cell, o
putting it into another cell and copy/paste over what you have
 
D

David McRitchie

Reiterating what you and Frank already said plus a couple more

Formula shows in cell instead of formatted value
-- make sure you do not have the cell formatted as text
select cell, format, cells, number (tab), look at category
-- make sure you are not in the formula view
tools, options, view (tab), make sure formulas is unchecked
-- make sure you don't have a single quote in front of cell, look at
the formula on the formula tool bar.
-- make sure your formula begins with an equal sign
Lotus 1-2-3 did not use an equal sign in front, Excel does
(don't use compatibility options)
-- make sure you do not have a space in front of the equal sign
if you have a lot of these to fix see TRIMALL macro in
http://www.mvps.org/dmcritchie/excel/join.htm#trimall


Making a formula(s) visible is often useful though
-- precede an example with a single quote to make it act as text, or
-- display the formula used in another cell with the GetFormula macro
in Show FORMULA or FORMAT of another cell
http://www.mvps.org/dmcritchie/excel/formula.htm
-- list of formula used via John Walkenbach's
Creating a List of Formulas (Tip 37)
http://j-walk.com/ss/excel/tips/tip37.htm

Formula works but can't be seen:
-- This is a protection feature to hide the formula, it is enabled by
Format, Cells, Protection(tab), turning on Hidden
and not effective until worksheet is protected with
tools, protection, worksheet

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
 
M

Miket6086

Changing the format to general seems to have worked. The result of th
formula is a cell formatted as text, so will the attributes transfe
through the general format? For example if my formula result needs t
be an integer or phone number which general would not cover? Or, i
the text cell format the only one that causes this problem?
I also did check for: space preceeding the formula, a single quot
preceeding, missing equal sign, and show formulas option in the vie
tab of options.
Thanks!
 
F

firefytr

well if you're entering phone numbers, one suggestion would be to ente
the number as such:

8885551212

(shown in the formula bar as such also)

format as Special -> Phone Number, to look like this:

(888)555-1212

and back to what McRitchie was saying, along with the advantages o
having a formula as text, if you'd like to have a short and concise wa
to evaluate that formula, download and install the morefunc add in b
Laurent Longre (here: http://longre.free.fr/english/index.html ) an
use the EVAL function (e.g. =EVAL(A1) - where A1 houses your textua
formula, preceded by a single apostrophe)
 
D

David McRitchie

When changing the format from a number format to at text format
or from a text format to a number format, you must reenter the
value for the format to take effect. F2 then Enter
Use of the TRIMALL macro is one way to effect a reentry.
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
for more information.

I would never format telephone numbers or zipcodes as numbers
instead I would use text. Becasue the formats vary from country to
country and you might want to include additional areas.

In fact even in the US with telephone area code no longer optional
I doubt that area code in parens will last much longer in preference to
800-555-1212

The advantage of showing the formula used in another cell is that
you know the formula works. If you try to evaluate a formula from
text, you don't know that the formula is valid and it will not be adjusted
if you insert/delete rows/columns. I do have both on my formula page.
--
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

IRS statistics for Tax Forms filed per state are in Excel files
http://www.irs.gov/taxstats/article/0,,id=96947,00.html
 
Top