Find and Custom Formatting in Excel

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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Michael FowlerSolutions ConsultantCommented:
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Curt LindstromCommented:
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.

tahirihAuthor Commented:
Thank you.
Curt LindstromCommented:
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

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.