+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    Registered User
    Join Date
    01-07-2021
    Location
    Texas
    MS-Off Ver
    2016
    Posts
    5

    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!

  2. #2
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010, 2019
    Posts
    2,219

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

    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.

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,342

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

    I did a small test
    Type 656 in cell A1
    Ctrl-F (Find and replace)
    Find what: 000656
    Match entire cell content: check
    Look in: Value

    Now it seach for 000656 only, although A1 value is 656.
    Attached Images Attached Images

  4. #4
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010, 2019
    Posts
    2,219

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

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Custom data validation with formula doesn't work & acts as if it was "any value"
    By MikeBlazej in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-04-2021, 08:29 AM
  2. Macro that changes # format doesn't work. Press "run," dialog box disappears, no error msg
    By OldManLearningExcel in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-04-2013, 11:42 PM
  3. Replies: 1
    Last Post: 08-18-2006, 08:45 AM
  4. Range("A1").select doesn't work!
    By pianoman in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-25-2006, 09:08 AM
  5. [SOLVED] Why doesn't 'Range("A65000").Select' work?
    By Alan in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-22-2006, 08:20 PM
  6. [SOLVED] [SOLVED] Excel macro convert to VBA - doesn't work, hangs on Range("Q35").Select
    By Harold Good in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-13-2006, 05:09 PM
  7. Excel macro convert to VBA - doesn't work, hangs on Range("Q35").Select
    By Harold Good in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-13-2006, 04:55 PM

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