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:

Share on facebook
Share on linkedin
Share on email
  • Anders

    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!

  • Gustav

    One way to solve it:

  • Emil


    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.

  • David

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

Leave a Reply