excel vba-提取2个字符之间的文本 [英] excel vba- extract text between 2 characters

问题描述

如果我有这个列：

ColA
-----
NUMBER(8,3)
NUMBER(20)

我需要一个VBA函数这些开始和结束字符串只会在单元格中出现一次）：

I need a VBA function that would go (note these start and end string would only ever appear once in a cell):

extract_val（cell，start_str，end_str）

extract_val(cell,start_str,end_str)

即。 extract_val（A1，（，）），并给出结果：

ie. extract_val(A1,"(",")") and give the results:

8,3
20

我只需要在其他vba代码中使用此功能，而不是将其作为公式放在工作表上。

I only need to use this function within other vba code not by putting it as a formula on the sheet.

更新（感谢答案，我解决了：）

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

推荐答案

您可以使用 instr 来定位字符串中的字符（例如，返回'（'的位置），然后可以使用 中间 使用'（'和'）'的位置提取变电站。

You can use instr to locate a character within the string (returning the position of '(' for example). You can then use mid to extract a substing, using the positions of '(' and ')'.

某些东西（从内存）：

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.

这篇关于excel vba-提取2个字符之间的文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！