在VBA/Excel 365中将文本列表转换为数字列表/数组

一、Excel 365 函数方案（无需VBA） #

利用Excel 365的动态数组函数组合，直接在单元格生成结果，无需编写代码。

核心公式 #

假设原字符串在单元格A1，输入以下公式即可生成逗号分隔的完整数字列表：

=TEXTJOIN(", ", TRUE, LET(
    parts, TEXTSPLIT(A1, " "),
    expanded, MAP(parts, LAMBDA(part,
        IF(ISNUMBER(SEARCH("to", part)),
            LET(
                start, TEXTBEFORE(part, "to"),
                after_to, TEXTAFTER(part, "to"),
                end_num, IFERROR(TEXTBEFORE(after_to, "by"), after_to),
                step, IFERROR(TEXTAFTER(after_to, "by"), 1),
                SEQUENCE((end_num - start)/step + 1, , start, step)
            ),
            part
        )
    )),
    TOCOL(expanded, TRUE)
))

公式解析 #

• TEXTSPLIT(A1, " ")：将原字符串按空格拆分为单个元素数组
• MAP(parts, LAMBDA(...))：遍历每个元素，分情况处理：
  • 不含to的普通数字：直接返回原数字
  • 含to的范围：解析起始数、结束数、步长（默认步长1），用SEQUENCE生成连续序列
• TOCOL(expanded, TRUE)：将嵌套数组扁平化并忽略空值
• TEXTJOIN(", ", TRUE, ...)：将扁平化后的数字数组合并为逗号分隔的字符串

如果需要返回数组而非字符串，直接取TOCOL(expanded, TRUE)部分即可。

二、VBA 方案 #

适合需要自定义逻辑或兼容旧版Excel的场景，以下是一个可直接调用的自定义函数：

自定义函数代码 #

Function ExpandNumberList(inputStr As String) As Variant
    Dim parts() As String
    Dim resultCol As New Collection
    Dim part As Variant
    Dim startNum As Long, endNum As Long, stepNum As Long
    Dim afterTo As String
    Dim i As Long
    
    ' 按空格拆分原字符串
    parts = Split(inputStr, " ")
    
    For Each part In parts
        If InStr(part, "to") > 0 Then
            ' 提取起始数字
            startNum = CLng(Left(part, InStr(part, "to") - 1))
            ' 处理to之后的内容
            afterTo = Mid(part, InStr(part, "to") + 2)
            
            ' 判断是否包含步长参数by
            If InStr(afterTo, "by") > 0 Then
                endNum = CLng(Left(afterTo, InStr(afterTo, "by") - 1))
                stepNum = CLng(Mid(afterTo, InStr(afterTo, "by") + 2))
            Else
                endNum = CLng(afterTo)
                stepNum = 1
            End If
            
            ' 生成序列并加入集合
            For i = startNum To endNum Step stepNum
                resultCol.Add i
            Next i
        Else
            ' 普通数字直接加入集合
            resultCol.Add CLng(part)
        End If
    Next part
    
    ' 将集合转为数组返回（可改为返回字符串）
    Dim outputArr() As Long
    ReDim outputArr(1 To resultCol.Count)
    For i = 1 To resultCol.Count
        outputArr(i) = resultCol(i)
    Next i
    
    ExpandNumberList = outputArr
    
    ' 若需返回逗号分隔字符串，替换上述数组代码为：
    ' ExpandNumberList = Join(Application.Transpose(outputArr), ", ")
End Function

使用方法 #

1. 按Alt+F11打开VBA编辑器，插入模块，粘贴上述代码
2. 在Excel单元格中输入=ExpandNumberList(A1)（A1为原字符串所在单元格），即可得到结果数组；若修改函数返回字符串，则直接显示逗号分隔的文本

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