Everything you need to master Excel BI Challenge 4
This is the fourth challenge in the Excel BI challenge series. We are asked to extract the words which are all capitals (Uppercase).
The answer should be as provided in the Excel file.
Here is the link to the base Excel BI challenge file. You can view the Excel BI LinkedIn post for the challenge HERE.
Giles has recorded his attempt at this challenge live; no prep, no fluff, just opening the file and getting going. This means that you get to watch Giles' thought process and his journey, "warts and all".
You can watch Giles' walkthrough video on our Excel on the Road Channel.
In this article, I am going to work through the core elements of the challenge. I recommend reading this article and then working through the video with the help of our supporting Excel file.
Please download the supporting Excel file. This file includes all of the formulas from Giles' walkthrough, complete with the full logic and workings with supporting guidance. It also covers alternative solutions including an Excel Tables-based version and a PowerQuery solution.
Before you get stuck into the video and supporting Excel file, let's step back and think about the logic behind this challenge.
The challenge:
We are asked to extract the words where every character is a capital (Uppercase).
To achieve this we will need to split the text into each separate word, then work out if every character in the word is a capital and, finally filter to only return words that are all in uppercase characters.
Step 1
First, we need to split the text to separate out each word.
=TEXTSPLIT(A2," ")
This uses the TEXTSPLIT function with a single space (" ") as a delimiter. TEXTSPLIT is a dynamic array function and therefore spills the results over the appropriate number of columns.
Step 2
Next, we need to repeat the first step but, at the same time, convert the text to all uppercase.
=UPPER(TEXTSPLIT(A2," "))
The UPPER function is used to convert text into all uppercase.
Step 3
We now need to compare the original text (step 1) to the uppercase version (step 2)
=EXACT(D2,G2)
EXACT is a logical formula. It returns TRUE if the items match exactly and FALSE if not. EXACT is case-sensitive so can be used here to compare the results of step 1 and step 2.
Step 4
The final step before bringing it all together is to filter out any words that include lowercase characters.
=FILTER(G2#, J2:K2 = TRUE)
To do this we use the FILTER function to return only rows where the result of step 3 is equal to TRUE. That is to say that the original text and uppercase version of the text are exactly the same. ie the original text was already all uppercase.
Step 5 - Finally, let's bring it all together
Bringing the elements from above together within a LET function gives us the following formula:
=LET(
Words, TEXTSPLIT(A2, " "),
UpperWords, UPPER(Words),
ExactMatch, EXACT(Words, UpperWords),
SplitOutput, FILTER(Words, ExactMatch = TRUE, ""),
Output, TEXTJOIN(" ", TRUE, SplitOutput),
Output
)
In this case, Giles' solution requires a formula for each row rather than being a true single-cell solution. Most people who attempted this solution also came up with a formula for each row.
I have shared a single-cell solution from Amardeep Singh in the supporting Excel file. This is what Anup's formula looks like:
=MAP(A2:A10,LAMBDA(x,
LET(
d,TEXTSPLIT(x," "),
r,IF(EXACT(d,UPPER(d)),d,""),
TEXTJOIN(" ",TRUE,r)
)
)
)
I then took this approach and applied it to Giles' row-based solution to provide a single cell version:
=MAP(A2:A10, LAMBDA(x,
LET(
Words, TEXTSPLIT(x, " "),
UpperWords, UPPER(Words),
ExactMatch, EXACT(Words, UpperWords),
SplitOutput, FILTER(Words, ExactMatch = TRUE, ""),
Output, TEXTJOIN(" ", TRUE, SplitOutput),
Output
)
I highly recommend that you work through the video alongside the Workings tab of the supporting Excel file and take your time to make sure that you understand each element.
I'll be honest, I was once again stumped by this challenge, mainly because I had never come across the EXACT function before.
I am finding it incredibly useful to work through Giles' videos and build these articles and the accompanying workbook. Challenging myself to work in a way that is outside my normal, comfortable modelling style is a lot of fun. I am learning a lot.
I hope you are too.
The Excel BI Challenges series has been created by Vijay A. Verma. It is an (ever-growing) set of Excel-based challenges. Each challenge is designed to test your mastery of Excel-based modelling.