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

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 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.

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

### IF blood pressure problem 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.

Thank you and see you around.

### Related articles:

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

### 33 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

• JasonW says:

This formula is beautiful! Very elegant!

• Ankur Sharma says:

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

• Vineet Sharma says:

I found some good content on your web also.

• Chandoo says:

Welcome back to the comments hero.

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

• Daniel Ferry says:

Matt, try this:

Function CodeFound(d\$, k\$)
Dim i&, j&
For i = 1 To Len(d)
If Mid(d, i, 1) = Mid(k, j + 1, 1) Then j = j + 1
Next
CodeFound = "No"
If j = Len(k) Then CodeFound = "Yes"
End Function

• Jeremy says:

Longest answer ever, I love it 🙂

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

• Chandoo says:

Those are some very interesting formulas. I liked the MID versions especially 🙂

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

25. Tony says:

=LET( a, FIND("0",B4), b, FIND("0",B4,a+1), c, FIND("7",B4,b+1), IF(ISNUMBER(a + b + c),"Yes","No"))

 « Number to Words – Excel Formula There are 20 Easter Eggs in this Workbook »

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.