fbpx

Is there a secret code in this data? [Excel Homework]

Excel Challenges - 32 comments

Are you ready for a fun Excel challenge? Read on then…

You are a nautical transport manifest analyst at New Donk city harbor. But you also have a secret identity. You are a spy for Global Intelligence Organization. As part of routine inspection of cargo details, you came across a list of shipping codes that look suspicious.

Your mission – Find the secret codes

Step 1: Download the sample data.

Step 2: The secret code is 007. If you find it anywhere in the shipping number, then it is a Yes.

For example: the shipment number 1230450789 has the code since you can spot 007 when reading from left to right. The code can be separated by other numbers, but as long as it is present in whole, the answer is Yes.

Step 3: You can use formulas, Power Query, VBA or rugged good looks to solve this.

Go ahead and post your solutions in the comments area.

Note: when posting your formula answers, just assume B4 has the shipment number.

Want more problems to solve?

As a transport manifest analyst life can be a bit drab. Worry not, I have several more challenges for you. Solve these problems (click on title or image).

Job title matching problem

Can you match the job titles?
Can you match the job titles?

Any repetitions in the number?

repetitive digits in the number...
One of them has too many fives

Elevator problem

Elevator problem - excel formula homework
Can the elevator go?

IF blood pressure problem

BP - have it or not?
BP – Have it or not?

Chandoo

Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.

Thank you and see you around.

Related articles:

Written by Chandoo
Tags:
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

32 Responses to “Is there a secret code in this data? [Excel Homework]”

  1. Charles Smith says:

    =IF(NOT(ISERR(FIND("0",B4)+FIND("0",B4,FIND("0",B4)+1)+FIND("7",B4,FIND("0",B4,FIND("0",B4)+1)))),"Yes","No")

  2. Shiv says:

    Make 4 columns. Find position of first 0 in First column using Find/Search Function, position of 2nd 0 using same function but string should be limited to after First 0. This can be achieved by using Len function and removing number of Characters in first column. In third, use Find to search 7 but string should be limited to after 2nd 0. That can be done by adding characters in 1st & 2nd column. Suitably insert IFERROR with some text if in case of error. In the fourth one, with the help of AND & ISNUMBER, find out True values, there you have your answer-Shipping Numbers with 007 in it! Sigh! pretty long solution though..

  3. p45cal says:

    In D4 copied down:
    =IF(ISNUMBER(SEARCH("*0*0*7*",B4)),"Yes","No")

    VBA:
    Sub blah()
    For Each cll In Range("B4:B23").Cells
    If cll.Value Like "*0*0*7*" Then cll.Offset(, 3) = "Yes" Else cll.Offset(, 3) = "No"
    Next cll
    End Sub

    VBA if you've got an awful lot:
    Sub blah2()
    With Range("B4:B23")
    x = .Value
    For rw = 1 To UBound(x)
    If x(rw, 1) Like "*0*0*7*" Then x(rw, 1) = "Yes" Else x(rw, 1) = "No"
    Next rw
    .Offset(, 3).Value = x
    End With
    End Sub

  4. Patrick Reagan says:

    That could simply be:
    =IFERROR(IF(SEARCH("*0*0*7*",B4)=1,"Yes"),"No")

    • neil R Auty says:

      You could leave out the =1 bit and still gives the correct answer.
      I went the long way about this, totally forgot wildcard search so did a long walking nested find solution...
      Nicely done

  5. Cratze says:

    =IFERROR(IF(FIND(7,B4,FIND(0,B4,FIND(0,B4)+1))>0,"Yes"),"No")

  6. Daniel Ferry says:

    {=IF(ISERR(SEARCH("*0*0*7*",B4)),"No","Yes")}

  7. Esakki Rajesh M says:

    =IF(ISERROR(FIND(7,MID(MID(B4,FIND(0,B4)+1,LEN(B4)),FIND(0,MID(B4,FIND(0,B4),LEN(B4)))+1,LEN(MID(B4,FIND(0,B4),LEN(B4)))))),"No","Yes")

  8. Latiff says:

    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")

    convoluted but works.

  9. Vineet says:

    Thanks, guys. Recalled my Linux programming days. I can't imagine that *x* could also be used here.

  10. Manoj Choudhary says:

    =IF(ISERROR(SEARCH(7,B4,SEARCH(0,B4,SEARCH(0,B4,1)+1)+1)),"No","Yes")

  11. neil R Auty says:

    =IFERROR(IF(FIND(7,B4,(FIND(0,B4,(FIND(0,B4))+1))+1),"Yes"),"No")

  12. Alexander says:

    =IF(IFERROR(FIND("007";SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B4;1;"");2;"");3;"");4;"");5;"");6;"");8;"");9;""));0)>0;"Yes";"No")

  13. p45cal says:

    I put together a Power Query solution to this a few days ago, but didn't post it because I'm new to Power Query and I don't know the full range of functions available within it, but since no-one has posted one I'll link to my rooky efforts here:
    https://app.box.com/s/f7rxwlg0g4etvk9ziweu7ztj8gz0qrbz
    where there's a couple of macros too (not related to the Power Query offering).

  14. JasonW says:

    Having look at the other solutions, I see I used a long way around:

    =IF(
    IFERROR(SMALL(UNIQUE(IFERROR(SEARCH(0,B4,SEQUENCE(LEN(B4))),"")),2),"")<
    IFERROR(MAX(UNIQUE(IFERROR(SEARCH(7,B4,SEQUENCE(LEN(B4))),""))),""),
    "Yes","No")

    Using wildcards is brilliant!

  15. Lee T says:

    =IF(IFERROR(SEARCH(7,B4),"No")="No","No",IF(ISERROR(SEARCH(7,B4,SEARCH(0,B4,SUM(SEARCH(0,B4)+1)))),"No","Yes"))
    1st time i have had some time to do the homework (wonder why that it is!), thought my solution was good until i saw the other posts, some really efficient coding. Room for me to improve!

  16. Matt Brook says:

    Option Explicit

    Public Const HIDDEN_MESSAGE As String = "007"

    Public Function IsSecretCodePresent(ByVal cellContents As Range) As String

    Dim secret() As String
    Dim message() As String
    Dim functionResult As String

    functionResult = ""

    secret() = SplitStringIntoStringArray(HIDDEN_MESSAGE)
    message() = SplitStringIntoStringArray(cellContents.Value)

    If IsCodeEmbedded(secret(), message()) Then
    functionResult = "Yes"
    Else
    functionResult = "No"
    End If

    IsSecretCodePresent = functionResult

    End Function

    Private Function SplitStringIntoStringArray(ByVal stringIn As String) As String()

    Dim temp() As String
    Dim i As Integer

    ReDim temp(Len(stringIn) - 1)

    For i = 1 To Len(stringIn)
    temp(i - 1) = Mid$(stringIn, i, 1)
    Next

    SplitStringIntoStringArray = temp()

    End Function

    Private Function IsCodeEmbedded(ByRef codeIn() As String, ByRef messageIn() As String) As Boolean

    Dim functionResult As Boolean
    Dim codeCounter As Integer
    Dim messageCounter As Integer
    Dim lastCounterValue As Integer
    Dim codeCount As Integer
    Dim checkArray() As Integer
    Dim checkCounter As Integer
    Dim currentCodeItem As String
    Dim currentMessageItem As String
    Dim individualCodesFound As Integer
    Dim messageLBoundValue As Integer
    Dim forceLoopJump As Boolean
    Dim forceLoopStop As Boolean

    functionResult = False
    lastCounterValue = 0
    forceLoopJump = False
    forceLoopStop = False

    ' Get the First Item in the message array
    messageLBoundValue = LBound(messageIn)

    ' Get number of items in the Seret Code
    codeCount = UBound(codeIn) - LBound(codeIn) + 1

    ' Create Array to keep Checks on each item in the Secret Code are present
    ReDim checkArray(0 To codeCount - 1)
    For checkCounter = LBound(checkArray) To UBound(checkArray)
    checkArray(checkCounter) = 0
    Next checkCounter

    ' Loop through items in Secret Code
    For codeCounter = LBound(codeIn) To UBound(codeIn)

    currentCodeItem = codeIn(codeCounter)

    ' Loop through message
    For messageCounter = messageLBoundValue To UBound(messageIn)

    currentMessageItem = messageIn(messageCounter)

    ' If there is a match, record position in message
    ' so in next item in Code can start in last
    ' position in message checked
    If currentMessageItem = currentCodeItem Then

    checkArray(codeCounter) = 1
    lastCounterValue = messageCounter
    forceLoopJump = True

    Exit For

    End If

    Next messageCounter

    If forceLoopJump = True Then
    messageLBoundValue = lastCounterValue + 1
    Else
    ' If we have cycled through message and haven't found
    ' the code, then exit function returning false
    If checkArray(codeCounter) = 0 Then
    forceLoopStop = True
    End If
    End If

    If forceLoopStop = True Then
    Exit For
    End If

    Next codeCounter

    ' Find out how many items of the Secret Code were found
    For checkCounter = LBound(checkArray) To UBound(checkArray)
    individualCodesFound = individualCodesFound + checkArray(checkCounter)
    Next checkCounter

    functionResult = (individualCodesFound = codeCount)

    IsCodeEmbedded = functionResult

    End Function

  17. Jeremy says:

    Simplest solution I found

    =IF(ISNUMBER(SEARCH("0*0*7",B4,1)),"Yes","No")

  18. Mark Williams says:

    This was the first challenge I've tried...happy to find a solution but very impressed with the other solutions here...

    =IF(IFERROR(FIND("7",B4,FIND("0",B4,FIND("0",B4)+1)+1),-1)>0,"Yes","No")

  19. Jim says:

    Another (and longer) version is this:
    =ISNUMBER(FIND("007",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B4,2,""),3,""),4,""),5,""),6,""),8,""),9,""),1))

  20. MichaelCH says:

    =IF(ISNUMBER(SEARCH("0*0*7",B4)),"Yes","No")
    =IF(ISERR(SEARCH("0*0*7",B4)),"No","Yes")
    =MID("YesNo",ISERR(SEARCH("0*0*7",B4))*3+1,3)

    UDF:
    Function Is_007(s As String) As String
    If s Like "*0*0*7*" Then Is_007 = "Yes" Else Is_007 = "No"
    End Function

  21. MichaelCH says:

    =CHOOSE(ISERR(SEARCH("0*0*7",B4))+1,"Yes","No")
    =IF(COUNT(SEARCH("0*0*7",B4)),"Yes","No")
    =MID("YesNo",4^ISERR(SEARCH("0*0*7",B4)),3)

  22. MichaelCH says:

    =IFERROR(IF(SEARCH("0*0*7",B4),"Yes"),"No")

  23. ELIENAY JUNIOR says:

    I used a matrix formula to solve this. I only know how to use matrix formulas to solve kkkk problems.

    =IF(SUM(IFERROR(IF((MID(LEFT(B4,MAX(IF(MID(B4,ROW(INDIRECT("1:"&LEN(B4))),1)+0=7,ROW(INDIRECT("1:"&LEN(B4))),""))),ROW(INDIRECT("1:"&LEN(B4))),1)+0)=0,1,""),""))>1,1,"")

  24. Irvine Russell says:

    I used the RegEx pack in VBA to create a UDF:

    Function simpleCellRegex(Myrange As Range, strPattern As String) As String
    Dim regEx As New RegExp
    Dim strInput As String
    Dim strReplace As String
    Dim strOutput As String

    If strPattern "" Then
    strInput = Myrange.Value
    strReplace = ""

    With regEx
    .Global = True
    .MultiLine = True
    .IgnoreCase = False
    .pattern = strPattern
    End With

    If regEx.test(strInput) Then
    simpleCellRegex = regEx.Replace(strInput, strReplace)
    Else
    simpleCellRegex = "Not matched"
    End If
    End If
    End Function

    and then used the following formula to test:
    =IF(simpleCellRegex(B4,"\d*0\d*0\d*7\d*")"Not Matched","Yes","No")

Leave a Reply


« »