获取括号之间的值，一个字符串中的多个匹配 [英] Get the value between the parentheses, multiple matches in one string

问题描述

我的电子表格有一列如下字符串：

 一些文本（text1）一些测试（text2）（text1 ）
  

如何获取括号之间的所有值？我正在寻找的结果是：

  text1，text2 
  

即使 text1，text2 ... testn 多次出现在单元格中，我需要在结果中只有一次。

我在这里找到了一个函数 GetParen ：

My spreadsheet has a column with value like this string:

some text (text1) some test (text2) (text1)

How do I get all values between parentheses? The result I am looking for is:

text1, text2

Even if text1, text2... testn is present in the cell multiple times, I need it in the result only once.

I found a function GetParen here: Get the value between the brackets

It is helpful, but it gives the fist available value in the parentheses and ignores the rest.

解决方案

It seems unwieldy to have one User Defined Function for individual entries and another for a collective result of all entries.

Paste the following into a standard module code sheet.

Function getBracketedText(str As String, _
                 Optional pos As Integer = 0, _
                 Optional delim As String = ", ", _
                 Optional dupes As Boolean = False)
    Dim tmp As String, txt As String, a As Long, b As Long, p As Long, arr() As Variant

    tmp = str
    ReDim arr(1 To 1)

    For b = 1 To (Len(tmp) - Len(Replace(tmp, Chr(40), vbNullString)))
        p = InStr(p + 1, tmp, Chr(40))
        txt = Trim(Mid(tmp, p + 1, InStr(p + 1, tmp, Chr(41)) - (p + 1)))
        If UBound(Filter(arr, txt, True)) < 0 Or dupes Then   '<~~ check for duplicates within the array
            a = a + 1
            ReDim Preserve arr(1 To a)
            arr(UBound(arr)) = txt
        End If
    Next b

    If CBool(pos) Then
        getBracketedText = arr(pos)
    Else
        getBracketedText = Join(arr, delim)
    End If
End Function

Use like any other native worksheet function. There are optional parameters to retrieve an individual element or a collection as well as changing the default <comma><space> delimiter.

    

这篇关于获取括号之间的值，一个字符串中的多个匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！