vba 如何从单元格中提取特定文本？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12548751/
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
How to extract specific text from a cell?
提问by Ken Ingram
In this case, I want to extract the beginning text in a cell and leave the remainder intact. e.g. a series of cells contain:
在这种情况下，我想提取单元格中的开始文本并保留其余部分不变。例如，一系列单元格包含：
2nd Unit. Miami
3rd Production Staff. Toronto
1st Ad. San Francisco
I want to break this up without using Text to columns as previous rows are formatted differently and these last few rows are outliers that I want to handle.
我想在不使用 Text to columns 的情况下将其分解，因为前几行的格式不同，而最后几行是我想要处理的异常值。
I thought Regular Expressions might do it, but that seems a bit complex.
我认为正则表达式可能会做到，但这似乎有点复杂。
My algorithm idea is: 1. grab the wanted text (what function or custom sub would do that?) 2. Past the text to it's new location 3. Cut the text from the cell, leaving the remaining text.
我的算法想法是： 1. 获取想要的文本（什么函数或自定义子会这样做？） 2. 将文本粘贴到它的新位置 3. 从单元格中剪切文本，留下剩余的文本。
Seems simple but I'm still wending my way through VBA forest, and at the rate I'm going it's going to end up faster doing it by hand. But this seems like a good opportunity to learn some VBA tricks.
看起来很简单，但我仍然在 VBA 森林中徘徊，按照我要去的速度，它最终会更快地手工完成。但这似乎是学习一些 VBA 技巧的好机会。
TIA
TIA
Update: I want to take the text up to the ".\ " and move it to a different column, keeping the remainder where it is.
更新：我想将文本移到“.\”并将其移动到不同的列，将其余部分保留在原处。
回答by Daniel
VBA is unnecessary. To get the text after .\ in cell A1: =MID(A1,FIND(".\",A1,1)+2,LEN(A1))to get the text before .\ in A1: =LEFT(A1,FIND(".\",A1,1)-1).
VBA 是不必要的。要获取单元格 A1 中 .\ 之后的文本：=MID(A1,FIND(".\",A1,1)+2,LEN(A1))获取 A1 中 .\ 之前的文本：=LEFT(A1,FIND(".\",A1,1)-1)。
As additional information, Findreturns the placement in the string where .\appears. It is the equivalent of InStrin VBA. If .\is not in the cell, it will display #VALUE, because I didn't bother to add error checking.
作为附加信息，Find返回字符串中.\出现的位置。它相当于InStr在 VBA 中。如果.\不在单元格中，它将显示#VALUE，因为我没有费心添加错误检查。
回答by chris neilsen
Since you seem to want to modify the cell text in place, VBA will be required.
由于您似乎想就地修改单元格文本，因此需要 VBA。
Inside a loop that sets clto the cell to be processed:
在设置cl为要处理的单元格的循环内：
str = cl.value
i = Instr(str, ".\")
cl = Trim(Mid$(str, i + 2)) ' assuming you want to exclude the ".\"
cl.Offset(0, 1) Trim(Left$(str, i - 1)) ' Places the original first part one cell to the right
回答by Ken Ingram
For the sake of anyone who had this same question, here is the fully tested, working code.
为了任何有同样问题的人，这里是经过全面测试的工作代码。
Function RE6(strData As String) As String
Dim RE As Object, REMatches As Object
Set RE = CreateObject("vbscript.regexp")
With RE
.MultiLine = False
.Global = False
.IgnoreCase = True
.Pattern = "[0-9][0-9][0-9][0-9]B"
RE6 = .test(strData)
End With
Set REMatches = RE.Execute(strData)
If REMatches.Count > 0 Then
RE6 = True
Else
RE6 = False
End If
End Function
Sub territory()
Dim strTest As String, str As String, cl As Range
strTest = ActiveCell.Value
Set cl = ActiveCell
If RE6(strTest) = True Then
str = cl.Value
i = InStr(str, ". ")
cl = Trim(Mid$(str, i + 2))
cl.Offset(0, 1) = Trim(Left(str, i - 1))
cl.Offset(0, 2) = "Instance"
MsgBox RE6(strTest)
End If
End Sub
