+ Reply to Thread
Results 1 to 2 of 2

Decimal point and following zeros show properly in cells, but not in Formula Bar.

  1. #1
    Registered User
    Join Date
    01-27-2013
    Location
    Charlotte, NC
    MS-Off Ver
    Excel 2007
    Posts
    1

    Decimal point and following zeros show properly in cells, but not in Formula Bar.

    I found out I had this problem because I was trying to do a Find and Replace on my worksheet. Numbers that I could clearly see on the spreadsheet weren't showing up in searches.

    Scenario:
    The Currency Format in the cells is for a decimal and two decimal places without the dollar sign.

    Example 1:
    What shows up in the cell is this: 3.00
    What shows up in the Formula Bar is this: 3

    Example 2:
    What shows up in the cell is this: 3.14
    What shows up in the Formula Bar is this: 3.14

    If I do a Find and Replace for 3.00, Excel can't find the data (that is because what shows in the Formula Bar is 3).
    But, if I do a Find and Replace for 3.14, Excel finds the value just fine.

    I did some sleuthing and JUST found that in the plain Find box, if I click to show Options, I have the option to change the Look In parameter to either Formulas, Values, or Comments. If I choose Values and do a find for 3.00, then 3.00 shows up. If I choose Formula and do a find for 3.00, Excel can't find the data.

    BUT ... in the Find and Replace box under Options, the Look In option has only Formula; there is no option to choose Values or Comments. Those options ARE in the Find box, but NOT in the Find and Replace box.

    It appears that in order to be able to complete a proper Find and Replace for numbers with trailing zeros, I either need for the Formula Bar to show the number 3.00 as it appears in the cell, or I need to have the Values parameter added to the Look In option.

    Thank you so much for your help!

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,085

    Re: Decimal point and following zeros show properly in cells, but not in Formula Bar.

    I'm not really sure what help you are asking for. Numbers don't have "trailing zeroes" as such, except in the display. If a number has decimal places, like 3.14, then Excel stores the number with the decimal places. If it is an integer, for example, 3, that's what it stores. The only reason you see trailing zeroes in the cell is because you have formatted the cell to show them.

    If you format all the cell as General, you will see the underlying number ... including decimal places, if the number has any. You'll also see that dates are, typically, 5 digit numbers ... not as displayed.

    So, the value shown in the formula bar is correct ... but so is the value shown in the cell. It's just the format that makes the difference.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1