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. 🙂

## 14 Responses to “How to find out if a text contains question? [Excel formulas]”

Can this be used to create an exclusion list?

Anything within this list comes back as TRUE so we know to exclude?

I have codes (w10, w11, w12) that I want to exclude; but if I enter W19 it comes back as TRUE when it should be FALSE

any help with this or what the limitations are with this.

Great post and has sparked a few ideas !

Hi Chandoo,

I got the first "*", what's the second "*" about (towards the end)? Please explain about both the "*" actually.

The '*' is a wildcard in Excel text searches. It will match with any text.

By having the stars around q.words, you are asking Excel to look for any text surrounding the words in the range.

If you only used the first star, you would only get 'TRUE' values if the question /ended/ with the words from q.words. Only using the second one would only get you TRUE values if the question /started/ with those words.

Thanks a ton xwingace. I almost forgot - wilcard characters.

Hi Chandoo,

It's great to see so you posting so often! Please keep them coming...

In terms of this exercise, is it oversimplifying to simply create a formula that searches for question marks?

Also, as "Question Words" can often be used even when a question isn't being asked (e.g. "That's what I was thinking." or "This reminds me of where we got married."), any tips on how to potentially account for those instances?

Keep up the great work!

One could also just look for a question mark in each cell. i.e. =IF(ISNUMBER(FIND("?",A1)),"1","") Then conditional format the cell red text if the test cell = 1. Questions can be worded many different ways (i.e. "Would you...", "Can one..." etc.).

Brad, you can use shortest formula DIRECTLY as conditional format rule: =ISNUMBER(FIND(“?”,A1))

That's all, no need of test cell.

Shouldn't you also look for "?" in addition to question words? Since that question itself would not be caught by your question words filter.

Great post though, I didn't know about this ability to search for multiple items in a countifs

What? No-one asked "Chandoo, will you marry me?" Un freakin believable.

How to convert numbers into text format. Like there are numbers in one column such as 100, 200, & so on & in the next colum what formula should be inputted to convert 100 into Hundred or 102 as One hundred & two and so on. Please help.

1. Open MS Excel Workbook

2. Click Alt + F11

Then, Microsoft Visual Basic Window opens

3. Click Alt + I, M (or Click Module from Insert Menu Bar)

Then, Module Window opens

4. Copy the below Code (Short Cut: Ctrl + C (or) Alt + E, C (or) Right Click, C (or) Ctrl + Insert)

'-----------

Option Explicit

'Main Function

Function NumWords(ByVal MyNumber)

Dim Rupees, Paises, Temp

Dim DecimalPlace, Count

ReDim Place(9) As String

Place(2) = " Thousand "

Place(3) = " Million "

Place(4) = " Billion "

Place(5) = " Trillion "

' String representation of amount.

MyNumber = Trim(Str(MyNumber))

' Position of decimal place 0 if none.

DecimalPlace = InStr(MyNumber, ".")

' Convert Paises and set MyNumber to Rupee amount.

If DecimalPlace > 0 Then

Paises = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _

"00", 2))

MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))

End If

Count = 1

Do While MyNumber ""

Temp = GetHundreds(Right(MyNumber, 3))

If Temp "" Then Rupees = Temp & Place(Count) & Rupees

If Len(MyNumber) > 3 Then

MyNumber = Left(MyNumber, Len(MyNumber) - 3)

Else

MyNumber = ""

End If

Count = Count + 1

Loop

Select Case Rupees

Case ""

Rupees = "No Rupees"

Case "One"

Rupees = "One Rupee"

Case Else

Rupees = Rupees & " Rupees"

End Select

Select Case Paises

Case ""

Paises = " and No Paises"

Case "One"

Paises = " and One Paise"

Case Else

Paises = " and " & Paises & " Paises"

End Select

NumWords = Rupees & Paises

End Function

' Converts a number from 100-999 into text

Function GetHundreds(ByVal MyNumber)

Dim Result As String

If Val(MyNumber) = 0 Then Exit Function

MyNumber = Right("000" & MyNumber, 3)

' Convert the hundreds place.

If Mid(MyNumber, 1, 1) "0" Then

Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "

End If

' Convert the tens and ones place.

If Mid(MyNumber, 2, 1) "0" Then

Result = Result & GetTens(Mid(MyNumber, 2))

Else

Result = Result & GetDigit(Mid(MyNumber, 3))

End If

GetHundreds = Result

End Function

' Converts a number from 10 to 99 into text.

Function GetTens(TensText)

Dim Result As String

Result = "" ' Null out the temporary function value.

If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19...

Select Case Val(TensText)

Case 10: Result = "Ten"

Case 11: Result = "Eleven"

Case 12: Result = "Twelve"

Case 13: Result = "Thirteen"

Case 14: Result = "Fourteen"

Case 15: Result = "Fifteen"

Case 16: Result = "Sixteen"

Case 17: Result = "Seventeen"

Case 18: Result = "Eighteen"

Case 19: Result = "Nineteen"

Case Else

End Select

Else ' If value between 20-99...

Select Case Val(Left(TensText, 1))

Case 2: Result = "Twenty "

Case 3: Result = "Thirty "

Case 4: Result = "Forty "

Case 5: Result = "Fifty "

Case 6: Result = "Sixty "

Case 7: Result = "Seventy "

Case 8: Result = "Eighty "

Case 9: Result = "Ninety "

Case Else

End Select

Result = Result & GetDigit _

(Right(TensText, 1)) ' Retrieve ones place.

End If

GetTens = Result

End Function

' Converts a number from 1 to 9 into text.

Function GetDigit(Digit)

Select Case Val(Digit)

Case 1: GetDigit = "One"

Case 2: GetDigit = "Two"

Case 3: GetDigit = "Three"

Case 4: GetDigit = "Four"

Case 5: GetDigit = "Five"

Case 6: GetDigit = "Six"

Case 7: GetDigit = "Seven"

Case 8: GetDigit = "Eight"

Case 9: GetDigit = "Nine"

Case Else: GetDigit = ""

End Select

End Function

'----------

5. Paste into the Module Window (Short Cut: Ctrl + V (or) Alt + E, V (or) Right Click, V)

6. Close Microsoft Visual Basic Window

Why SUMPRODUCT and not just SUM?

You can use either. SUM would require entering the formulas as array formula (CTRL+Shift+Enter). SUMPRODUCT can process arrays by default.

Is there a way to see the array that the COUNTIFS (without the SUMPRODUCT) would return?