excel vba-提取2个字符之间的文本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7293461/
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- extract text between 2 characters
提问by toop
If i had this column:
如果我有这个专栏：
ColA
-----
NUMBER(8,3)
NUMBER(20)
I need a VBA function that would go (note these start and end string would only ever appear once in a cell):
我需要一个可以运行的 VBA 函数（注意这些开始和结束字符串只会在单元格中出现一次）：
extract_val(cell,start_str,end_str)
提取值（单元格，start_str，end_str）
ie. extract_val(A1,"(",")") and give the results:
IE。extract_val(A1,"(",")") 并给出结果：
8,3
20
I only need to use this function within other vba code not by putting it as a formula on the sheet.
我只需要在其他 vba 代码中使用这个函数，而不是将它作为公式放在工作表上。
UPDATE (thanks to the answer, i settled on:)
更新（感谢答案，我决定：）
---------------------------
Public Function extract_value(str As String) As String
Dim openPos As Integer
Dim closePos As Integer
Dim midBit As String
On Error Resume Next
openPos = InStr(str, "(")
On Error Resume Next
closePos = InStr(str, ")")
On Error Resume Next
midBit = mid(str, openPos + 1, closePos - openPos - 1)
If openPos <> 0 And Len(midBit) > 0 Then
extract_value = midBit
Else
extract_value = "F"
End If
End Function
Public Sub test_value()
MsgBox extract_value("NUMBER(9)")
End Sub
回答by paxdiablo
You can use instrto locate a character within the string (returning the position of '('for example). You can then use midto extract a substing, using the positions of '('and ')'.
您可以使用instr定位字符串中的字符（'('例如返回位置）。然后，您可以使用和mid的位置来提取子字符串。'('')'
Something like (from memory):
像（根据记忆）：
dim str as string
dim openPos as integer
dim closePos as integer
dim midBit as string
str = "NUMBER(8,3)"
openPos = instr (str, "(")
closePos = instr (str, ")")
midBit = mid (str, openPos+1, closePos - openPos - 1)
You may want to add error checking in case those characters don't occur in the string.
如果这些字符没有出现在字符串中，您可能需要添加错误检查。
回答by EIV
If the string is “Value of A is [1.0234] and Value of B is [3.2345]”
如果字符串是“A 的值为 [1.0234]，B 的值为 [3.2345]”
If you want to extract the value of Bi.e., 3.2345, then
如果要提取 B 的值，即 3.2345，则
firstDelPos = InStrRev(textline, “[“) ‘ position of start delimiter
secondDelPos = InStrRev(textline, “]”) ‘ position of end delimiter
stringBwDels = Mid(textline, firstDelPos + 1, secondDelPos – firstDelPos – 1) ‘ extract the string between two delimiters
MsgBox (stringBwDels) ‘ message shows string between two delimiters
回答by Zv Lfdl
If I have a cucumber table as test data in a cell, and need to access the value under 'Header 1' between 4th and 5th pipes, below is how I done it. My table example as below in cell D7 :
如果我有一个黄瓜表作为单元格中的测试数据，并且需要访问第 4 个和第 5 个管道之间“标题 1”下的值，下面是我是如何做到的。我在单元格 D7 中的表格示例如下：
*Cell D7:
*单元格D7：
code to access 'abcd' which is found after 4th occurrence of '|' and before 5th occurrence of '|'
访问 'abcd' 的代码，该代码在第 4 次出现 '|' 后找到 并且在第 5 次出现“|”之前
Dim sheet5 As Worksheet
Dim i As Integer
Dim k As Integer
Dim openPos As Long
Dim clsPos As Long
Dim textBetween as String
'Using for loop to find 4th occurrence of pipe '|' for openPos
For i = 1 To 4
openPos = InStr(openPos + 1, sheet5.Range("D7"), "|", vbTextCompare)
Next i
'Using for loop to find 5th occurrence of pipe '|' for clsPos
For k = 1 To 5
clsPos = InStr(clsPos + 1, sheet5.Range("D7"), "|", vbTextCompare)
Next k
'Displaying the value between openPos and clsPos
txtBetween = Mid(sheet5.Range("D7").Value, openPos + 1, clsPos - openPos - 1)
MsgBox ("Current Header 1 value: " & txtBetween)
