如何提取文本字符串中的文本

Question

我有一个简单的问题，我希望在不使用 VBA 的情况下解决它，但如果这是解决它的唯一方法，那就这样吧。

我有一个包含多行（均为一列）的文件。每行的数据如下所示：

1 7.82E-13 >gi|297848936|ref|XP_00| 4-氢氧化物 gi|297338191|gb|23343|随机随机

2 5.09E-09 >gi|168010496|ref|xp_00| 2-丙酮酸

等...

我想要的是某种方法来提取以“gi|”开头的数字字符串并以“|”结尾。对于某些行，这可能意味着多达 5 个 gi 编号，而对于其他行，则只有 1 个。

我希望输出看起来像这样：

297848936,297338191

168010496

等等...

Answer 1

这是使用正则表达式对象的非常灵活的 VBA 答案。该函数的作用是提取它找到的每个子组匹配（括号内的内容），并用您想要的任何字符串分隔（默认为“，”）。 可以在此处找到有关正则表达式的信息：http://www.regular-expressions.info/

您 会这样称呼它，假设第一个字符串位于 A1 中：

=RegexExtract(A1,"gi[|](\d+)[|]")

因为这会查找所有出现的“gi|”接下来是一系列数字，然后是另一个“|”，对于问题中的第一行，这将为您提供以下结果：

297848936, 297338191

只需将其沿着列运行即可，您就完成了！

Function RegexExtract(ByVal text As String, _
                      ByVal extract_what As String, _
                      Optional separator As String = ", ") As String

Dim allMatches As Object
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
Dim i As Long, j As Long
Dim result As String

RE.pattern = extract_what
RE.Global = True
Set allMatches = RE.Execute(text)

For i = 0 To allMatches.count - 1
    For j = 0 To allMatches.Item(i).submatches.count - 1
        result = result & (separator & allMatches.Item(i).submatches.Item(j))
    Next
Next

If Len(result) <> 0 Then
    result = Right$(result, Len(result) - Len(separator))
End If

RegexExtract = result

End Function

Answer 2

这是（假设数据在 A 列中）

=VALUE(LEFT(RIGHT(A1,LEN(A1) - FIND("gi|",A1) - 2),
FIND("|",RIGHT(A1,LEN(A1) - FIND("gi|",A1) - 2)) -1 ))

不是最好的公式，但它可以提取数字。

我刚刚注意到，因为每行有两个值，输出用逗号分隔。您需要检查是否有第二个匹配项、第三个匹配项等，以使其适用于每个单元格的多个数字。

参考您的确切样本（假设每个单元格最多有 2 个值），以下代码将起作用：

=IF(ISNUMBER(FIND("gi|",$A1,FIND("gi|", $A1)+1)),CONCATENATE(LEFT(RIGHT($A1,LEN($A1)
- FIND("gi|",$A1) - 2),FIND("|",RIGHT($A1,LEN($A1) - FIND("gi|",$A1) - 2)) -1 ), 
", ",LEFT(RIGHT($A1,LEN($A1) - FIND("gi|",$A1,FIND("gi|", $A1)+1) 
- 2),FIND("|",RIGHT($A1,LEN($A1) - FIND("gi|",$A1,FIND("gi|", $A1)+1) - 2)) 
-1 )),LEFT(RIGHT($A1,LEN($A1) - FIND("gi|",$A1) - 2),
FIND("|",RIGHT($A1,LEN($A1) - FIND("gi|",$A1) - 2)) -1 ))

怎么样？ VBA 解决方案可能更适合您，但我将把它留在这里给您。

要达到 5 个数字，请研究模式并在公式中手动递归。它会变得很长！

Answer 3

我可能会首先使用将文本转换为列向导在 | 分隔符上拆分数据。
在 Excel 2007 中，位于数据选项卡上的数据工具组，然后选择文本到列。指定 其他： 和 | 作为分隔符。

从您发布的示例数据来看，执行此操作后，数字将全部位于同一列中，因此您可以删除不需要的列。

Answer 4

由于其他人提出了没有 VBA 的解决方案...我将介绍确实使用的解决方案。现在，您可以决定是否使用它。

刚刚看到@Issun 提出了正则表达式的解决方案，非常好！无论哪种方式，都将仅使用“简单”VBA 为问题提供“适度”的解决方案。

Option Explicit
Option Base 0

Sub findGi()

    Dim oCell As Excel.Range
    Set oCell = Sheets(1).Range("A1")

    'Loops through every row until empty cell
    While Not oCell.Value = ""

        oCell.Offset(0, 1).Value2 = GetGi(oCell.Value)
        Set oCell = oCell.Offset(1, 0)

    Wend

End Sub

Private Function GetGi(ByVal sValue As String) As String

    Dim sResult As String
    Dim vArray As Variant
    Dim vItem As Variant
    Dim iCount As Integer

    vArray = Split(sValue, "|")
    iCount = 0

    'Loops through the array...
    For Each vItem In vArray

        'Searches for the 'Gi' factor...
        If vItem Like "*gi" And UBound(vArray) > iCount + 1 Then

            'Concatenates the results...
            sResult = sResult & vArray(iCount + 1) & ","

        End If

        iCount = iCount + 1

    Next vItem

    'And removes trail comma
    If Len(sResult) > 0 Then

        sResult = Left(sResult, Len(sResult) - 1)

    End If

    GetGi = sResult

End Function

Answer 5

在 Google 表格中打开 Excel 并使用正则表达式 REGEXEXTRACT

示例使用

=REGEXEXTRACT("My favorite number is 241, but my friend's is 17", "\d+")

提示：在此示例中，REGEXEXTRACT 将返回 241，因为它返回第一个匹配的大小写。

在你的情况下

=REGEXEXTRACT(A1,"gi[|](\d+)[|]")