Find and Custom Formatting in Excel

Posted on 2011-10-04
Last Modified: 2012-06-21
Why is it when I have custom formatted a column in excel 2010 to be '00###' I cannot find 01003. I do understand that in this case, the original cell was 1003, but under this formatting, Excel nicely matched the total number of # signs (instead of forcing two xeros in the front).

Hope this made sense.

Thank you.
Question by:tahirih
    LVL 23

    Accepted Solution

    The default for excel search is to look in formulas. When this is selected it appears to search for the cell content before formatting. If you select options and change "Look in" to values the search will work as required

    LVL 18

    Assisted Solution

    by:Curt Lindstrom
    To get 001003 you need the custom format to be 000###
    The zeroes means number of digits and not necessarily a zero. Excel only inserts a zero if the number is shorter than the specified length. 00### means that you want a 4 digit number entered to be displayed with 5 digits.


    Author Closing Comment

    Thank you.
    LVL 18

    Expert Comment

    by:Curt Lindstrom
    The definition of 0 and # in excel's help file:

       Format Symbol      Description/result

       0                  Digit placeholder. For example, if you type 8.9 and
                          you want it to display as 8.90, then use the
                          format #.00

       #                  Digit placeholder. Follows the same rules as the 0
                          symbol except Excel does not display extra zeros
                          when the number you type has fewer digits on either
                          side of the decimal than there are # symbols in the
                          format. For example, if the custom format is #.## and
                          you type 8.9 in the cell, the number 8.9 is


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Suggested Solutions

    Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now