求ARRAYTOTEXT(,1)的最优VBA逆实现函数

要实现Excel原生ARRAYTOTEXT(array,1)的逆解析，核心难点是精准区分引号内的分隔符（逗号/分号）与数组结构的分隔符——直接文本拆分会误拆引号内的特殊字符，因此必须通过正则表达式匹配元素，同时兼容多场景数据。

完整VBA代码 #

Option Explicit

' 解析ARRAYTOTEXT(,1)生成的文本，返回对应溢出数组
Function ARRAYTOTEXT_INV(inputText As String) As Variant
    Dim regEx As Object
    Dim matches As Object
    Dim elements As Variant
    Dim rowDelimiters As Variant
    Dim rowCount As Long, colCount As Long
    Dim i As Long, j As Long
    Dim outputArr As Variant
    
    ' 初始化正则对象
    Set regEx = CreateObject("VBScript.RegExp")
    regEx.Global = True
    regEx.IgnoreCase = False
    
    ' 去除首尾大括号，校验格式
    inputText = Trim(inputText)
    If Not (Left(inputText, 1) = "{" And Right(inputText, 1) = "}") Then
        ARRAYTOTEXT_INV = inputText
        Exit Function
    End If
    inputText = Mid(inputText, 2, Len(inputText) - 2)
    
    ' 正则匹配所有元素：含双引号转义的字符串、数字、嵌套集合、空值
    regEx.Pattern = """([^""]|"""")*""|[^,;]+|(?<=;|^)(?=,;|$)"
    Set matches = regEx.Execute(inputText)
    
    ' 收集并处理元素格式
    ReDim elements(0 To matches.Count - 1)
    For i = 0 To matches.Count - 1
        elements(i) = matches(i).Value
        ' 处理带引号的元素：去首尾引号，还原内部转义双引号
        If Left(elements(i), 1) = """" And Right(elements(i), 1) = """" Then
            elements(i) = Mid(elements(i), 2, Len(elements(i)) - 2)
            elements(i) = Replace(elements(i), """""", """"")
        End If
        ' 标记无引号的空值
        If elements(i) = "" Then elements(i) = Empty
    Next i
    
    ' 计算数组行数（未被引号包裹的分号数量+1）
    regEx.Pattern = "(?<!"");(?!""|$)"
    Set rowDelimiters = regEx.Execute(inputText)
    rowCount = rowDelimiters.Count + 1
    If rowCount = 0 Then rowCount = 1
    
    ' 计算列数（元素总数/行数）
    colCount = (UBound(elements) + 1) / rowCount
    
    ' 构建输出二维数组
    ReDim outputArr(1 To rowCount, 1 To colCount)
    For i = 1 To rowCount
        For j = 1 To colCount
            outputArr(i, j) = elements((i - 1) * colCount + (j - 1))
        Next j
    Next i
    
    ' 返回溢出数组
    ARRAYTOTEXT_INV = outputArr
End Function

支持的核心场景 #

• 无引号数字：直接解析为数值类型，例如{1,2;3,4}还原为2行2列的数值区域
• 无引号空白值：例如{,"";,}还原为2行2列的空值区域
• 嵌套集合：例如{"{""a,"", ""b"";""a,"", ""b""}","{""c,"", ""d"";""c,"", ""d""}"}还原为包含嵌套数组文本的单元格
• 特殊字符元素：兼容元素含逗号、分号或内部引号的场景，例如{"=FIND("","",A1)",";"}还原为原公式和符号文本

使用示例 #

假设单元格C1存储ARRAYTOTEXT(A1:B2,1)的结果：{1,"Semicolon ; in me";"Comma, in me",4}，在任意单元格输入=ARRAYTOTEXT_INV(C1)，将自动溢出还原为与A1:B2完全一致的2行2列区域。

内容的提问来源于stack exchange，提问作者CamalotCoder