How to find out if a text contains question? [Excel formulas]

Few days back, I ran my first ever webinar, on a topic called, “How to be a BETTER Analyst?” (here is the replay link, in case you missed it).  It was a huge success. More than 1,100 people attended the live webinar and hundreds more watched the replay. As part of the webinar, we had interactive Q&A. Viewers posted their questions and I replied to as many of them as I can.

After the webinar, I wanted to make sure I covered all the questions. So I downloaded the chat history. There were more than 700 messages in it. And I am not in the mood to read line by line to find-out the questions. A good portion of chat messages were not questions but stuff like ‘hello everyone, I am from Idaho’, ‘Wow, Chandoo has beard!”, “Enjoying a beer in Belgium while watching webinar” etc. So I wanted a quick way to flag the messages as question or not.

I did what any sensible Excel analyst would do.

I made myself a hot cup of coffee, started playing games on my iPhone while sipping it.

Of course after a cup of coffee and a bout of Candy Crush, I wrote simple Excel formula to find-out if the text in a cell is question or not. Let me share the formula & logic with you.

Let’s take a look at the data

This is how the downloaded chat history looked like when imported to Excel. The column on right is where we need to write formula to find out if the comment is a question or not.

For the sake of simplicity, assume this data is in column B, starting with cell B5.

The logic for identifying questions

In real life, finding if the other person is asking a question or just saying something can be tricky. For example, last evening my wife said, “Shall we go shopping?” and I assumed it was a question and said “no”. Apparently, it wasn’t a question. You can guess the rest.

Unlike real life, in Excel, we can come up with good enough approximation to nail down questions.

For example, if a cell contains any of the below words, we can say it is a question.

What, why, how, who, when, where, is it, can I, can you, which, is this, are you, can we, are we, am I

This is a pretty good way to separate questions from non-questions.

Let’s assume all the question words are maintained in a named range called q.words

Writing the formula

So here is the formula to check if a cell contains question or not.

=SUMPRODUCT(COUNTIFS(B5,"*"&q.words&"*"))>0

How does this formula work?

Remember, B5 is the cell in question (no pun).

We need to see anywhere in B5, one of the question words occur.

This is where COUNTIFS formula helps. It can count how many times a value has occurred in a range.

In our case, if B5 contains any of the question words. Note that B5 can contain other text too (apart from question words).

The formula COUNTIFS(B5,"*"&q.words&"*") will return an array of size 15 (as q.words contains 15 question words).

Let’s assume B5 has the text – “Why didn’t you take your wife to shopping?”

So, our COUNTIFS(B5...) will return the array {0;1;0;0;0;0;0;0;0;0;0;0;0;0;0}

The second item is 1 because second question word is Why.

If B5 has this text – “How I wish I took my wife to shopping. Can I take her now?”

COUNTIFS(..) will return this array {0;0;1;0;0;0;0;1;0;0;0;0;0;0;0} because it found the question words How and Can I.

But we don’t want the array…

You are right. We don’t need the array of 15 elements. We just want to know if any of the questions are present in the B5 cell.

So, we pass this array to SUMPRODUCT, which sums up all the numbers and tells us single value.

We then check if this value is >0 or not.

So there you have it. A formula to find out if a cell has question or not.

A question for you…

Do you conduct text analysis using Excel? What techniques do you use? Please share your approach & formulas in the comments.

Bonus material for text analysis using Excel

If you deal with lots of text data, you will find below resources very useful.

• Finding patterns in Text – case study problem
• Extracting file name from full path
• Analyzing search keywords & finding word frequency
• Analyzing 20,000 comments – Case study
• Sentiment analysis of text using Excel
• More on text processing & analysis using Excel

Now if you excuse me, I need to take my wife for shopping. 🙂