140|EXCEL – Word Crunch

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