Excel VBA - 删除最多*word*的字符串内容
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18402931/
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
Excel VBA - delete string content up to *word*
提问by jcv
I'm trying to delete string content up to a certain word contained within the string. For example
我正在尝试删除字符串中包含的某个单词的字符串内容。例如
"Emily has wild flowers. They are red and blue."
I'd like to use VBA in order to replace that with
我想使用 VBA 来替换它
"They are red and blue."
i.e. remove all the content up to the word "They". I don't know the string content and the number of characters contained in it.
即删除“他们”一词之前的所有内容。我不知道字符串内容和其中包含的字符数。
I'm not sure how to do this and I'd really appreciate your help!
我不知道该怎么做，非常感谢您的帮助！
采纳答案by S Nash
Here you go:
干得好：
Dim s As String
s = "Emily has wild flowers. They are red and blue."
Dim indexOfThey As Integer
indexOfThey = InStr(1, s, "They")
Dim finalString As String
finalString = Right(s, Len(s) - indexOfThey + 1)
回答by xQbert
Simple example of dropping all text before value in string.
删除字符串中值之前的所有文本的简单示例。
Sub Foo()
Dim strOrig As String
Dim strReplace As String
strOrig = "The Quick brown fox jumped over the lazy dogs"
strReplace = "jumped"
MsgBox (DropTextBefore(strOrig, strReplace))
End Sub
Public Function DropTextBefore(strOrigin As String, strFind As String)
Dim strOut As String
Dim intFindPosition As Integer
'case insensitive search
'could made it so that case sensitivity is a parameter but this gets the idea across.
If strOrigin <> "" And strFind <> "" Then
intFindPosition = InStr(UCase(strOrigin), UCase(strFind))
strOut = Right(strOrigin, Len(strOrigin) - (intFindPosition + Len(strFind) - 1))
Else
strOut = "Error Empty Parameter in function call was encountered."
End If
DropTextBefore = strOut
End Function
回答by Vasim
If the word is fixed, like "They" in the above example, you can simply do
如果这个词是固定的，比如上面例子中的“他们”，你可以简单地做
- CTRL + H (Replace)
- *They (your word with a star)
- CTRL + H（替换）
- *他们（你用星星的话）
in the Find box. The star * is a wildcard character which can be called as - of anything before or after (if added at end) the word.
在查找框中。星号 * 是一个通配符，它可以被称为 - 在单词之前或之后（如果添加到末尾）的任何内容。
Cautious: Take care when you have duplicate words in the same cell.
小心：在同一个单元格中有重复的单词时要小心。
回答by Vasim
This worked great for me:
这对我很有用：
Sub remove_until()
Dim i, lrowA, remChar As Long
Dim mString As String
lrowA = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lrowA
mString = Cells(i, 1).Value
If InStr(mString, "They") > 0 Then
remChar = Len(mString) - InStr(mString, "They") + 1
Cells(i, 2).Value = Left(mString, Len(mString) - remChar)
End If
Next
End Sub
