从 Excel 单元格中提取多个子字符串

Question

我有一个格式很糟糕的列，其中每个单元格可以包含零个或一个或多个条目，如下所示（这个包含两个）：

ACTI-U-9754 - Some description MDCF-U-9791 - Some other description

我需要将 11 个字符串提取到单独的列中，最好使用公式。对于上面的单元格，它应该如下所示：

ACTI-U-9754
MDCF-U-9791

我还没有找到处理这种特定场景的例子。

Answer 1

恐怕我想不出一个简单的公式方法，但是，这里有一个使用 RegEx 的 VBA 方法，希望对您有用。RegEx 模式假设代码始终相同，当然4 letters - 1 letter - 4 digits您可以根据需要进行修改。如果字母和数字的假设不正确，但格式始终为 4-1-4，您可以改用.{4}\-.\-.{4}：

Sub GetCodes()
    Dim strPattern: strPattern = "\w{4}\-\w\-\d{4}"   'Pattern to match
    Dim colNumber: colNumber = 1                        'Column number containing strings (In this case, 1, for column A)
    Dim rowCount: rowCount = 1                          'Row number to start from
    Range("B1").Select                                  'Cell to start new column from

    'Create a new RegEx engine instance
    Dim rgx: Set rgx = CreateObject("vbscript.regexp")

    'Set out RegEx instance to allow Global (More than 1 result per text), MultiLine (Incase there are any carriage returns in the cell), IgnoreCase (Allow both upper and lowercase, which isn't needed with \w but included to be sure) and Pattern, the patter defined above.
    With rgx
        .Global = True
        .MultiLine = True
        .IgnoreCase = True
        .Pattern = strPattern
    End With

    'Begin a loop that ends once we hit an empty cell
    Do
        'Get all our RegEx matches and store them in rgxMatches
        Dim rgxMatches: Set rgxMatches = rgx.Execute(Cells(rowCount, colNumber).Value)
        Dim rgxMatch
        'Loop through our matches
        For Each rgxMatch In rgxMatches
            'Write the match into the active cell
            ActiveCell.Value = rgxMatch.Value
            'Go down one row, ready to write the next cell if there is one
            ActiveCell.Offset(1, 0).Select
        Next

        'Increment our row count so the next loop uses the next row
        rowCount = rowCount + 1
    Loop Until IsEmpty(Cells(rowCount, colNumber))

    'Clean up after
    Set rgx = Nothing
    Set rgxMatches = Nothing
End Sub