vba - 在列中每个单元格的特定单词之后查找字符串？

Question

我需要帮助在句子中查找名称。在 A 列中，我有很多句子，每个句子可能包含一个“PM:{name}”子字符串，如下所示:



有些单元格可能不包含这个，但对于那些我想在 A 列中每个句子旁边的 B 列中打印字符串名称的单元格。

这是我到目前为止所拥有的，但它仅适用于单元格 A2。我需要它适用于 A 列中所有具有“PM:名称”的单元格。(我现在手动编写了 Sara)

Option Explicit

Sub PMName()
    Dim ws As Worksheet

    Dim Reg1 As Object
    Dim RegMatches As Variant
    Dim Match As Variant
    Dim NextWord As Boolean
    Dim LR As Long

    Dim i As Long
    For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
        Set Reg1 = CreateObject("VBScript.RegExp")
        With Reg1
            .Global = True
            .IgnoreCase = True
            .Pattern = "\w{1,50}"
        End With

        Set RegMatches = Reg1.Execute(Cells(i, 1).Value)
        NextWord = False '
        If RegMatches.Count >= 1 Then
            For Each Match In RegMatches
                If NextWord Then
                    Cells(i, 2).Value = Match
                    Exit Sub
                End If
                If UCase(Match) Like "PM" Then NextWord = True
            Next Match
        End If
    Next i
End Sub

Answer 1

我认为您也可以在没有正则表达式的情况下执行此操作:

Option Explicit

Sub foo()
Dim cl As Range
Dim i As Long, index As Long
Dim thisSentence As String
Dim words() As String

For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
    ' normalize our sentence, upper-case and replace consecutive spaces
    thisSentence = Replace(UCase(Cells(i, 1).value), "  ", " ")
    words = Split(thisSentence, " ")
    index = arrayIndex(words, "PM:")
    If index >= 0 Then
        Cells(i, 2).value = words(index + 1)
    End If
Next
End Sub

Function arrayIndex(words() As String, value As String) As Long
' NOTE: If "PM:" is the LAST item in the words array, this will return a -1 value
'       because there is no "name" to return.
'
Dim ret As Long
Dim i As Long
ret = -1
For i = LBound(words) To UBound(words) - 1
    If words(i) = value Then
        ret = i
        GoTo EarlyExit
    End If
Next
arrayIndex = ret
End Function

输出: