Find and Select doesn't work for entries with custom format "000000"

Hello,

I am a biological technician who uses PIT (passive integrated transponders) in small critters to track range, growth, etc. Each pit tag comes with a unique code 15 digits in length. However, we typically only use the last 6 digits as an ID since it is very unlikely we would get repeats in our closed system. My problem is that for some of our older PIT tags the last 6 digits contain leading zeros (i.e. 000825, 005678, 04757). It is essential that the leading zeros are there to distinguish between individuals. The solution to keep leading zeros is simple enough, I just used custom format "000000", however, when is this custom format Excel's Find and Select feature doesn't work. If I have a tag reading 000656 and I enter into the search bar 000656, the search won't return anything. Of course if input just 656 it will find 656s everywhere in this gargantuan data set. We need to be able to track each individual re-captured based on their 6 digit code back in time to track growth rates. I have tried formatting as text but it appears the original data handler didn't actually punch in zeros, leaving some entries as a 3 digit code. I hope I am making sense here, and if anyone has a solution to my problem it is greatly appreaciated!

Thanks!

When you format a cell, it does not physically change the value in the cell, so a cell containing 656 formatted to display 000656 still has a value of 656. The solution is in the Find dialog box... click the "Options>>" button and put a check mark in the "Match entire cell contents" CheckBox and then do your search for 656.

As I said, those leading zeros do not really exist in the cell, only the 656 does... the zeros are put there by the formatting, I could have had the formatting display anything I wanted in the cell but he value in the cell will always be 656... that is what you have to search for - the actual value in the cell, not the mirage created by the formatting.