使用 VBA 查找段落中文本的位置

Question

我有一个给定的文本和一个段落，确切的文本存在于段落中。考虑第一个带有索引 0 的单词，第二个带有索引 1 的单词，依此类推。我想在段落中找到文本的开始和结束索引。

我编写了存在的字数，段落的开始和结束索引的代码，但完全卡在这个代码中

这是查找段落起始索引的代码

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

这没有用质疑..

考虑包含 4 列的示例

1)该段

2)段落中出现的确切答案

3)段落中的答案开始索引

4)段落中的答案结束索引

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.   

对于开始和结束索引，只需从 0、1、2 开始计算段落中的单词,... 如果可以解决此问题，请帮助我使用上面的VBA代码。

Answer 1

这应该是你想要的：

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

结果是：

• 起始指数： 6
• 结束索引： 20