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

Posted on July 17th, 2015 in Excel Howtos , Learn Excel - 14 comments

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.

finding-if-cell-has-question-in-it

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.

question-wordsUnlike 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.

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

Written by Chandoo
Tags: , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

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

  1. james says:

    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 !

  2. Rahul says:

    Hi Chandoo,

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

    • xwingace says:

      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.

  3. DE says:

    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!

  4. Brad says:

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

    • Maxim says:

      Brad, you can use shortest formula DIRECTLY as conditional format rule: =ISNUMBER(FIND(“?”,A1))
      That's all, no need of test cell.

  5. Jim says:

    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

  6. Jeff Weir says:

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

  7. Hitesh says:

    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.

    • Gaurav V. says:

      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

  8. JW says:

    Why SUMPRODUCT and not just SUM?

  9. Pedro says:

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

Leave a Reply