vba 获取括号之间的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8058032/
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
Get the value between the brackets
提问by Andrei Ion
I have a column with some stuff that looks like the following string: V2397(+60)
我有一列包含一些类似于以下字符串的内容： V2397(+60)
How do I get the value between the brackets? In this case the +60.
如何获得括号之间的值？在这种情况下，+60.
The number (and character) before the brackets is not something standardized and neither the number between the brackets (it can be 100, 10 -10 or even 0...).
括号前的数字（和字符）不是标准化的，括号之间的数字也不是标准化的（可以是 100、10 -10 甚至 0...）。
回答by Andrew Cooper
VBA code:
VBA代码：
cellValue = "V2397(+60)"
openingParen = instr(cellValue, "(")
closingParen = instr(cellValue, ")")
enclosedValue = mid(cellValue, openingParen+1, closingParen-openingParen-1)
Obviously cellValueshould be read from the cell.
显然cellValue应该从单元格中读取。
Alternatively, if cell A1 has one of these values, then the following formula can be used to extrcat the enclosed value to a different cell:
或者，如果单元格 A1 具有这些值之一，则可以使用以下公式将封闭的值提取到不同的单元格：
=Mid(A1, Find("(", A1)+1, Find(")",A1)-Find("(",A1)-1)
回答by brettdj
I would use a regular expression for this as it easily handles
我会为此使用正则表达式，因为它很容易处理
- a no match case
- multiple matches in one string if required
- more complex matches if your parsing needs evolve
- 不匹配的情况
- 如果需要，一个字符串中有多个匹配项
- 如果您的解析需要发展，更复杂的匹配
The Testsub runs three sample string tests
本Test次运行三个示例字符串测试
The code below uses a UDF which you could call directly in Excel as well, ie = GetParen(A10)
下面的代码使用了一个 UDF，你也可以直接在 Excel 中调用它，即 = GetParen(A10)
Function GetParen(strIn As String) As String
Dim objRegex As Object
Dim objRegMC As Object
Set objRegex = CreateObject("vbscript.regexp")
With objRegex
.Pattern = "\((.+?)\)"
If .Test(strIn) Then
Set objRegMC = .Execute(strIn)
GetParen = objRegMC(0).submatches(0)
Else
GetParen = "No match"
End If
End With
Set objRegex = Nothing
End Function
Sub Test()
MsgBox GetParen("V2397(+60)")
MsgBox GetParen("Not me")
MsgBox GetParen(ActiveSheet.Range("A1"))
End Sub
回答by Paul Buitenhuis
Thanks to Andrew Cooper for his answer.
感谢安德鲁·库珀的回答。
For anyone interested I refactored into a function...
对于任何感兴趣的人，我重构为一个函数......
Private Function GetEnclosedValue(query As String, openingParen As String, closingParen As String) As String
Dim pos1 As Long
Dim pos2 As Long
pos1 = InStr(query, openingParen)
pos2 = InStr(query, closingParen)
GetEnclosedValue = Mid(query, (pos1 + 1), (pos2 - pos1) - 1)
End Function
To use
使用
value = GetEnclosedValue("V2397(+60)", "(", ")" )
回答by phoog
Use InStrto get the index of the open bracket character and of the close bracket character; then use Midto retrieve the desired substring.
使用InStr得到开括号字符和右括号字符的索引; 然后用于Mid检索所需的子字符串。
Using InStr$and Mid$will perform better, if the parameters are not variants.
如果参数不是变体，使用InStr$和Mid$会表现得更好。
