• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

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.
0
tahirih
Asked:
tahirih
  • 2
2 Solutions
 
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

Michael
0
 
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.

Regards,
Curt
0
 
tahirihAuthor Commented:
Thank you.
0
 
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
                      displayed.

Regards,
Curt
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now