Extract Question from String of varying Text

[FanofExcel18]

Trying to extract a question from a string of free form text in Excel.

Expected Result: How many licks to the center of a tootsie roll pop?

Side question, what if there are multiple?

Example:

This is a test of what I am looking for. How many licks to the center of a tootsie roll pop? Trying to see if this is possible. What if there are 2 questions with varying degree? Appreciate any assistance!

Truly appreciate any assistance!

 

[richh]

Oh concatenation, how I hate thee.

You can use an INSTR function to piece apart the string, but it would take a lot of finessing to really get down into the string and pull out a the sentence pertaining to the question.

Sub GetQuestions()
    Dim myText      As String
    Dim textSlice   As String
    Dim remainText  As String
    Dim foundQuest  As Boolean
    
    foundQuest = False


    myText = "This is a test of what I am looking for. " & _
        "How many licks to the center of a tootsie roll pop? " & _
        "Trying to see if this is possible. " & _
        "What if there are 2 questions with varying degree? " & _
        "Appreciate any assistance!"
        
    If InStr(myText, "?") = 0 Then
        'There isn't a question to be found
        Exit Sub
    End If
        
    textSlice = Left(myText, InStr(1, myText, "?"))
    
    remainText = Mid(myText, InStr(1, myText, "?") + 1)
    
CheckSlice:
    While foundQuest = False
        If InStr(1, textSlice, ".") > 0 Then
            textSlice = Mid(textSlice, InStr(1, textSlice, ".") + 1)
            
        Else
            foundQuest = True
             textSlice = Application.WorksheetFunction.Clean(VBA.Trim(textSlice))
            'The sotre textSlice somewhere.
            MsgBox textSlice
        End If
    Wend
    
    foundQuest = False
    
    While foundQuest = False
        If InStr(1, remainText, "?") > 0 Then
            textSlice = Left(remainText, InStr(1, remainText, "?"))
            remainText = Mid(remainText, InStr(1, remainText, "?") + 1)
            GoTo CheckSlice
        Else
            foundQuest = True
        End If
    Wend

End Sub

 

[taurean]

Disclaimer: I am not a RegExp expert so you will have to test this and see if it meets your requirement.

Place this in an inserted General Module

Public Function GetQuestion(strInput As String)
    Dim objRgEx As Object
    Dim objMatches, objMatch
    Set objRgEx = CreateObject("VBScript.RegExp")
    With objRgEx
        .Global = True
        .MultiLine = True
        .Pattern = "\..+?\?"
        Set objMatches = .Execute(strInput)
        For Each objMatch In objMatches
            GetQuestion = Trim(Mid(objMatch, 2, Len(objMatch)))
        Next
    End With
End Function

and then use it like a standard formula:

=GetQuestion(A3)