Secret Code Challenge

Secret Code Challenge

In our first quiz the task is simple – but not too easy: find the secret codes in a list of shipment numbers using Excel!

Download the workbook:


The code hidden in the shipment numbers is 007. Use Excel to come up with a solution that tells how many of the shipment numbers have a secret code in them.


Write your solution and answer in the comments! 🙂



Our solution:




4 thoughts on “Secret Code Challenge

  1. Multiple helper columns:

    Position of 1st 0: =IFERROR(FIND(B$2,$A3,1),””)
    Position of 2nd 0: =IFERROR(IF($B3″”,FIND(C$2,$A3,$B3+1),””),””)
    Position of 7 after second 0: =IFERROR(FIND(D$2,$A3,$C3+1),””)
    3 digit code check: =IF(LEN(TEXTJOIN(“”,TRUE,B3:D3))=3,1,””)
    Code Sequence Check: =IF(AND(B3<C3,C3<D3),"YES","NO")
    Code present: =IF(E3=1,F3,"NO")

    Probably not the optimal solution, but works!

  2. One way to solve it:
    =IFERROR(IF(FIND(7;B4;FIND(0;B4;FIND(0;B4;1)+1))>0;”Yes”;”No”);”No”)

  3. @Gustav

    Awesome! Almost identical to our solution!

    In the IF – function you actually don’t need to perform a logical test, since an integer by it self will return True.

  4. Denna verkar funka fint: =OM(OMFEL(SÖK(“0*0*7″;B4;1);0)>0;”Yes”;”No”)

Leave a Reply

You must be logged in to post a comment.

More blog posts

Tired of Googling?
Get the best Excel tips every week. Subscribe to our newsletter.

About the author

Emil har jobbat på Learnesy som produktutvecklare i flera år och ligger bakom flera av Learnesys kurser. Han har en utbildning inom datavetenskap med ett stort intresse för det analytiska och teoretiska. Utöver Finska och Svenska snackar Emil även flytande Excelfunktioner!