Excel VBA正则表达式函数可将多个匹配项返回到单个单元格中 [英] Excel VBA Regex Function That Returns Multiple Matches Into A Single Cell

问题描述

我需要帮助来使我的Excel函数正常工作.目的是运行一个单元内函数，该函数将从另一个单元的输入中提取一个正则表达式函数的所有模式匹配项到一个单元中，而不是一个单元阵列中.

I need help getting my Excel function to work. The goal is to run an in-cell function that extracts all pattern matches of a regex function from the input of another cell into one cell, not an array of cells.

我已经尝试过使用一个数组，该数组在函数对话框预览中返回两个匹配项，但仅在单元格中输出第一个匹配项.我也尝试过使用收藏，但是没有运气.

I have tried this using an array which returns two matches in the function dialogue box preview but only outputs the first match in the cell. I have also tried using a collection but had no luck with that.

这是我当前的代码以及用作函数的字符串输入的文本示例:

Here is my current code and a sample of text that would be used as the function's string input:

Function RegexMatches(strInput As String) As Variant

Dim rMatch As Match
Dim arrayMatches
Dim i As Long

arrayMatches = Array()

With New RegExp
    .Global = True
    .MultiLine = True
    .IgnoreCase = True
    .Pattern = "(Code)[\s][\d]{2,4}"
        For Each rMatch In .Execute(strInput)
        ReDim Preserve arrayMatches(i)
        arrayMatches(i) = rMatch.Value
        i = i + 1
    Next
End With

    RegexMatches = arrayMatches
End Function

从Excel单元格中采样strInput:

Sample strInput from an Excel cell:

代码123一些随机文本
去这里并继续下一行
代码4567后跟更多文字
包括换行而不仅仅是换行的文字


此函数所需的输出将是正则表达式函数的两个(2)匹配值进入单个单元格(例如，"Code 123 Code 4567").

Code 123 some random text
goes here and continues to the next line
Code 4567 followed by more text
including new lines not just wrapped text


The desired output from this function would be both (2) matched values from the regex function into a single cell (e.g. "Code 123 Code 4567").

任何帮助将不胜感激！

推荐答案

您好像错过了函数的结尾(根据Mat的杯子的评论)?尝试一下(按照Wiktor的评论).

Looks like you missed off the end of your function (as per Mat's Mug's comment)? Try this (which is as per Wiktor's comment).

根据Mat's Mug的建议进行了修改.

amended in light of Mat's Mug's suggestion.

Function RegexMatches(strInput As String) As String

Dim rMatch As Object
Dim s As String
Dim arrayMatches()
Dim i As Long

With New RegExp
    .Global = True
    .MultiLine = True
    .IgnoreCase = True
    .Pattern = "(Code)[\s][\d]{2,4}"
    If .test(strInput) Then
        For Each rMatch In .Execute(strInput)
            ReDim Preserve arrayMatches(i)
            arrayMatches(i) = rMatch.Value
            i = i + 1
            's = s & " " & rMatch
        Next
    End If
End With

RegexMatches = Join(arrayMatches, " ")

End Function

这篇关于Excel VBA正则表达式函数可将多个匹配项返回到单个单元格中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！