Menu
Is there a secret code in this data? [Excel Homework]
Excel Challenges - 33 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
Any repetitions in the number?
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:
|
Leave a Reply
| « Number to Words – Excel Formula | There are 20 Easter Eggs in this Workbook » |
At Chandoo.org, I have one goal, "to make you awesome in Excel & Power BI". I started this website in 2007 and today it has 1,000+ articles and tutorials on data analysis, visualization, reporting and automation using Excel and Power BI. 
33 Responses to “Is there a secret code in this data? [Excel Homework]”
=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")
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..
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
This formula is beautiful! Very elegant!
Liked Your Formula!
That could simply be:
=IFERROR(IF(SEARCH("*0*0*7*",B4)=1,"Yes"),"No")
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
=IFERROR(IF(FIND(7,B4,FIND(0,B4,FIND(0,B4)+1))>0,"Yes"),"No")
{=IF(ISERR(SEARCH("*0*0*7*",B4)),"No","Yes")}
I found some good content on your web also.
Welcome back to the comments hero.
=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")
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.
Thanks, guys. Recalled my Linux programming days. I can't imagine that *x* could also be used here.
=IF(ISERROR(SEARCH(7,B4,SEARCH(0,B4,SEARCH(0,B4,1)+1)+1)),"No","Yes")
=IFERROR(IF(FIND(7,B4,(FIND(0,B4,(FIND(0,B4))+1))+1),"Yes"),"No")
=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")
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).
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!
=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!
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
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
Longest answer ever, I love it 🙂
Simplest solution I found
=IF(ISNUMBER(SEARCH("0*0*7",B4,1)),"Yes","No")
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")
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))
=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
=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)
=IFERROR(IF(SEARCH("0*0*7",B4),"Yes"),"No")
Those are some very interesting formulas. I liked the MID versions especially 🙂
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,"")
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")
=LET( a, FIND("0",B4), b, FIND("0",B4,a+1), c, FIND("7",B4,b+1), IF(ISNUMBER(a + b + c),"Yes","No"))