vba 提取特定单词后的文本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42654332/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Extract text after specific word
提问by Gaus Shaikh
I want to retrieve the text immediately after the word "for" from a string. In the example below, I want to retrieve "GLDSK8716".
我想在字符串中的“for”一词之后立即检索文本。在下面的示例中，我想检索“GLDSK8716”。
Completion Notification for GLDSK8716 - Derivative Contracts - Futures, Forwards, Swaps, and Options
GLDSK8716 - 衍生合约 - 期货、远期、掉期和期权的完成通知
I tried this formula but it retrieves all of the text after "for".
我试过这个公式，但它检索“for”之后的所有文本。
=TRIM(MID(R2,SEARCH("For",R2)+LEN("for"),255))
回答by Vityata
This is my version with VBA:
这是我的 VBA 版本：
Option Explicit
Public Function ExtractAfterWord(rngWord As Range, strWord As String) As String
On Error GoTo ExtractAfterWord_Error
Application.Volatile
Dim lngStart As Long
Dim lngEnd As Long
lngStart = InStr(1, rngWord, strWord)
If lngStart = 0 Then
ExtractAfterWord = "Not Found"
Exit Function
End If
lngEnd = InStr(lngStart + Len(strWord) + 1, rngWord, " ")
If lngEnd = 0 Then lngEnd = Len(rngWord)
ExtractAfterWord = Trim(Mid(rngWord, lngStart + Len(strWord), lngEnd - lngStart - 2))
On Error GoTo 0
Exit Function
ExtractAfterWord_Error:
ExtractAfterWord = Err.Description
End Function
The condition in the code (if lngEnd = 0) is to make sure, that the formula works even if the for is before the last word in the text. A "Not Found" answer is also added.
代码 ( if lngEnd = 0) 中的条件是确保即使 for 位于文本中的最后一个单词之前，公式也能正常工作。还添加了“未找到”答案。
回答by Shai Rado
My solution is in VBA. The code below uses the RegExto find all words between spaces. Then it loops through all words, once it finds "for" it raises a flag to indicate the next word (RegEx.Match) is the one you are looking for.
我的解决方案是在 VBA 中。下面的代码使用RegEx来查找空格之间的所有单词。然后它循环遍历所有单词，一旦找到“for”，它就会引发一个标志以指示下一个单词 ( RegEx.Match) 是您要查找的单词。
Code
代码
Option Explicit
Sub ExtractNumbers()
Dim Reg1 As Object
Dim RegMatches As Variant
Dim Match As Variant
Dim NextWord As Boolean
Set Reg1 = CreateObject("VBScript.RegExp")
With Reg1
.Global = True
.IgnoreCase = True
.Pattern = "\w{1,50}" ' Match any set of characters up to length of 50, untill a space
End With
Set RegMatches = Reg1.Execute(Range("R2").Value)
NextWord = False ' <-- reset flag
If RegMatches.Count >= 1 Then
For Each Match In RegMatches
If NextWord Then
MsgBox "The word you are looking for is '" & Match & "'"
Exit Sub
End If
If UCase(Match) Like "FOR" Then NextWord = True '<-- "for" found >> raise flag up
Next Match
End If
End Sub
Screen-shot of your string in Cell R2 and result in a MsgBox:
单元格 R2 中字符串的屏幕截图，结果为MsgBox：
回答by Pankaj Jaju
Try =MID(A1,FIND("for ",A1)+4,FIND(" ",A1,FIND("for ",A1)+4)-(FIND("for ",A1)+4))
尝试 =MID(A1,FIND("for ",A1)+4,FIND(" ",A1,FIND("for ",A1)+4)-(FIND("for ",A1)+4))
Assuming your text is in cell A1 and the pattern is for <data> -
假设您的文本在单元格 A1 中并且模式是 for <data> -
