Finding position of a text in a paragraph using VBA

Question

I have a given text and a paragraph, the exact text is present in the paragraph. Considering first word with index 0, second with index 1 and so on.. I want to find the start and end index of the text in the paragraph.

I am written the code for number of words present, start and end index of paragraph, but totally stuck in this code

this is the code to find the starting index of a paragraph

=B1+LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+1

Thoe this is of no use to question..

Consider the Example with 4 column

1)The paragraph

2)the exact answer present in paragraph

3)Answer start index in paragraph

4)Answer end index in paragraph

Paragraph                  |  Answer             | StartIndex| EndIndex 

Hello Lorem ipsum Hello    |amet, consectetur    |     6     |   20
dolor sit amet, consectetur|adipisicing elit,    |           | 
adipisicing elit, sed do   |sed do eiusmod tempor|           |
eiusmod tempor incididunt  |incididunt ut labore |
ut labore et dolore magna  |et dolore magna      |
aliqua. Ut enim ad minim   |aliqua               |
veniam, quis nostrud       |
exercitation ullamco labor | 
nisi ut aliquip ex Hello   |
ea commodo consequat.   

For Start and end index just count the words in the paragraph from 0,1,2,... Please help me with the VBA code for above if this can be solved.

Answer 1

This should be what you want:

Sub TestIt()
    Const WHOLE_TEXT As String = "Hello Lorem ipsum Hello dolor sit amet, consecteturadipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim  veniam, quis nostrud exercitation ullamco labor nisi ut aliquip ex Hello ea commodo consequat."
    Const SEARCH_TEXT As String = "amet, consecteturadipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua"

    Dim startIndex As Long
    Dim endIndex As Long
    If FindText(WHOLE_TEXT, SEARCH_TEXT, startIndex, endIndex) Then
        Debug.Print "StartIndex: " & startIndex & vbNewLine &  "EndIndex: " & endIndex
    Else
        Debug.Print "Not found."
    End If
End Sub

'Returns True if searchText has been found.
'The *index parameters are ByRef because they will contain the results.
Function FindText(ByVal wholeText As String, ByVal searchText As String, ByRef outStartIndex As Long, ByRef outEndIndex As Long) As Boolean
    Dim substringPos As Long
    substringPos = InStr(wholeText, searchText)

    If substringPos = 0 Then Exit Function

    outStartIndex = UBound(Split(Trim(Left(wholeText, substringPos - 1)), " ")) + 1

    outEndIndex = UBound(Split(Trim(searchText), " ")) + 1 + outStartIndex

    FindText = True
End Function

Result is:

• StartIndex: 6
• EndIndex: 20