Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cell shows formula not value (Excel 2000)

    I have been seeing cells on workbooks that constantly show the cell formula instead of the cell value, no matter how I format it. Then, after pointing and clicking ad nauseum, the cell suddenly changed to show the value. So I cannot determine what I did to restore normality to it. What would cause a cell to constantly show its formula (but not in Ctrl+~ mode)? Thanks

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Cell shows formula not value (Excel 2000)

    This is not normal Excel behaviour if you say it is not CTRL+~.
    The only other time formulas display in the sheet is when you do not type a "=" sign in front!
    I assume this was a once off scenario...or is it a regular occurence in your workbook?
    Regards,
    Rudi

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell shows formula not value (Excel 2000)

    Maybe something here will give a clue to this behaviour.

    Alan

  4. #4
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell shows formula not value (Excel 2000)

    That's fine...thanks. I'll look into all those items. And the problem would not involve any hidden characters, would it? I have ASAP Utilites to help with those problems on my stuff, but I often have to help a co-worker solve such problems. (The TRIM function would remove such items, correct?) Thanks to all.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell shows formula not value (Excel 2000)

    The usual cause of this behavior is that the cell was formatted as Text when the formula was entered into the cell. If you enter a formula into a cell that is formatted as Text, the formula becomes a text string not a formula. Just changing the cell format to something will not change that text string into a formula. To get the text string to convert to a formula, you have to change the cell format to a numeric format (General for example), then with the cell selected press F2 and then press enter.
    Legare Coleman

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell shows formula not value (Excel 2000)

    The TrimALL macro is described here. There appears to be a number of issues regarding what method does what to various characters. Are there many instances (workbooks?) affected by this problem? If so, it might be worth us knocking out a macro to deals with the issue. Is it possible to post an affected section of such a worksheet?

    Alan

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell shows formula not value (Excel 2000)

    I would agree that the cell was likely formatted as text...we encountered this problem several times in the past when working with some of our spreadsheets.

    Stealing from a post from sdckapr a couple of weeks ago, and even from pieterse back in August 2002, you should be able to

    - select offending cells (one column at a time)
    - Data, text-to-columns, fixed width option, Finish

    One of these posts specified the fixed width option, the other specified the Delimited option, so I'm going to guess that it doesn't really matter which you choose.

    Hope that helps...

    - Brett

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell shows formula not value (Excel 2000)

    It could matter a great deal which option you choose. If any of the formula contain the delimiter character(s), the results will not be what you wanted. You definitely want the fixed width option.
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •