BYU Student Author: @Marco, @Millie_K_B
Reviewers: @Marta_Ellsworth
Estimated Time to Solve: 60 Minutes
We provide the solution to this challenge using:
Need a program? Click here.
Overview
After getting all your work done at The Data Analytics Guys LLC, you find yourself bored and looking for something to do. You’ve been wanting to learn more about Excel with matching string to improve your skills, so you look for a list of random words online. While trying to think of something to match the words to, your mind thinks of TechHub.training’s recent launch and so you decide to match the words with the letters in “techhub” in honor of it.
Instructions
Your output should only show the words that are made up of letters in “techhub”. Filter out words that are less than 3 characters long. The words must contain the letter “u” in them. The letters can be used more than once.
For an extra challenge, show the character length of each word and sort the data by the highest character length. You might want to use PowerBI for this extra step.
Data Files
Suggestions and Hints
Given the limitations of Excel’s FIND function, you will want to check for what letters don’t match each word. If a word is only made of the letters in “techhub,” what letters will it not include?
Using the Name function with intercepts will make formula writing easier.
Other formulas that might be useful include IFERROR, TRIM, TEXTJOIN, and TEXTSPLIT.
Solution
I took about 30 minutes to complete the challenge which I give a very high rating (5 stars). It was not too difficult but definitely required some thinking and helped me learn some new techniques. I have copied my solutions below
I used these 3 Excel functions
=IF(SUMPRODUCT(–ISNUMBER(SEARCH(MID(B3,ROW(INDIRECT(“1:”&LEN(B3))),1),“TECHUB”)))=LEN(B3),“Yes”,“No”)
=IF(LEN(B2)>3,“Yes”,“No”)
=IF(AND(C3=“Yes”, D3=“Yes”), B3, “”)
letters in “techhub”
=IF(OR(ISNUMBER(SEARCH(“t”, B3)), ISNUMBER(SEARCH(“e”, B3)),ISNUMBER(SEARCH(“c”, B3)),ISNUMBER(SEARCH(“h”, B3)),ISNUMBER(SEARCH(“u”, B3)),ISNUMBER(SEARCH(“b”, B3))),B3, “”)
length >3
=IF(LEN(D3)<3,“”,D3)
includes “u”
=IF(ISNUMBER(SEARCH(“u”, E3)), E3, “”)
Time to complete: 60 minutes.
Rating: Intermediate.
This was an engaging challenge, as I have been wanting to become more proficient with using If statements in Excel, rather than in coding languages like Python.
Solution: =SEARCH(“t”,B3) (Performed for each letter in “techhub”
=LEN(B3) to find the length of the words
=IF(ISNUMBER(G3),1,0) to test if the words had “u”
=OR(ISNUMBER(C3),ISNUMBER(D3),ISNUMBER(E3),ISNUMBER(F3),ISNUMBER(H3)) to test if any of the other letters (t, e, c, h, b) were present in the word
=IF(I3>=3,IF(K3=1,IF(L3=TRUE,B3))) to sort out the words that were longer than three characters, had a “u”, and had any of the other letters in techhub.
Time to complete: 40-60mins
This challenge wants only words with the letters found in the word “techhub”, so it doesn’t want words with any other letters in it.
I took it in 3 steps. (After all formulas I just filled down)
-
I filtered the list of words so that it would not include words that are less than 3 characters long:
- =IF(LEN(B3)<3,“Yes”,“No”)
so if the length of the word in B3 is greater than 3 it outputs No, and if its less than 3 it outputs yes
1.5. I made another list for the filtered words, so if the output was no if copied the word into another list, called filtered list
- =IF(C3=“No”, B3, “”)
-
I checked each character in every word for the letters in “techub”
- =IF(D3<>“”, IF(SUMPRODUCT(–(ISNUMBER(SEARCH(MID(D3,ROW(INDIRECT(“1:”&LEN(D3))),1),“techub”))))=LEN(D3), “Yes”, “No”), “No”)
This checks each character, and if it only contains letters in “techub” the output is Yes. If the word was letters that aren’t in “techub” or the cell is blank, the output is “No”
- I then made another list of the words that had the output of Yes
- =IF(E3=“Yes”, D3, “”)
(and) - =TEXTJOIN(" ", TRUE, G3:G674)
Overall, step 2 was the hardest part of challenge and it took me a long time to figure out. I really liked this challenge, it wasn’t too hard that I was pulling my hair out, but was hard enough were it was rewarding to finish!
Time to complete: 25 minutes
Difficulty: Easy
Challenge140_Word_Crunch_Excel.xlsx (18.6 KB)
I filtered the words column to display the words that contained the letters in the word “techhub.” Then, for the bonus challenge I used the LEN function to count the number of letters in each cell. Lastly, I used a filter to sort the data in the word length column to display the words with the most number of letters first and the words with the least number of letters last. Overall, I did not have much of a problem completing the challenge.
Time to complete: 30 minutes
Rating: Intermediate
I filtered the words based on a custom filter that “contains t?e?c?h? or contains u?b?”. After that, I used a formula to filter out words that are less than three characters. All the words already contained “u”, therefore, a filter was not needed. Overall, it was a great challenge to complete.
Hi! I used Data-> Filter. Select text filer
- must contain the letter “u”
- & must be greater than *3 (characters)
Thank you!
This took me less than 60 min.
I did ctrl+shift+down arrow to highlight all of the data. Went to filter and created a customer filter “?u” and less than or equal to “*3”, which means containing the letter “u” and less than or equal to 3 characters.
Thank you!