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.

The VBA function `InStr()` returns the start index. For example, `InStr(paragraph, answer)` returns a number like `15` which will be the start index. Then, the end index will just be this number you've got `+ Len(answer)`. — Matteo NNZ, Apr 01 '19 at 05:42
Are you sure you're searching on a VB Editor and not on the Excel sheet? It is a very basic built-in function, it would be very strange if you didn't have it. — Matteo NNZ, Apr 01 '19 at 05:49
Its is working but it is working character wise and not word based as I want index based on word not on character — andro, Apr 01 '19 at 06:15
If you don't share with us what you're trying to do, it's going to be hard to help you more. Please enrich your questions with the details of what you're trying to do, so someone can help you. Please note that function works with words, for sure. — Matteo NNZ, Apr 01 '19 at 06:33
Just re-read your last comment and figure out you're trying to count words, not just search for them. You will have to implement your own function, where you: 1) Get the index of the sentence you look for; 2) Extract the string coming before; 3) Split it by space; the length of that array will be your start word; 4) Split by space the sentence itself and add that length to the previous to find the last word. — Matteo NNZ, Apr 01 '19 at 06:43

AHeyne · Accepted Answer · 2019-04-01T06:48:02.323

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

Finding position of a text in a paragraph using VBA

1 Answers1