Excel 2016：拆分含字符串与数字的单元格为独立列

方案一：Office 365/Excel 2021 无VBA方案 #

利用内置的REGEX.REPLACE和TEXTSPLIT函数，一步完成拆分：

在B1单元格输入以下公式，按回车后会自动溢出到右侧列：

=TEXTSPLIT(TRIM(REGEX.REPLACE(REGEX.REPLACE(A1, "(-?\d+)", "|$1|"), "\|+", "|")), "|")

公式说明： #

1. REGEX.REPLACE(A1, "(-?\d+)", "|$1|")：匹配所有数字（包括负数格式-?\d+），在数字前后插入分隔符|，将原字符串转为类似Word |1| words |1| group of words |-2|的格式。
2. REGEX.REPLACE(..., "\|+", "|")：合并连续的|分隔符，避免因数字前后空格导致的多重分隔。
3. TRIM(...)：去除字符串首尾的空格和多余分隔符。
4. TEXTSPLIT(..., "|")：按|拆分字符串，自动将各部分填充到右侧列。

方案二：旧版Excel 无VBA兼容方案 #

旧版Excel无REGEX.REPLACE和TEXTSPLIT，可使用FILTERXML结合字符串替换实现：

在B1单元格输入以下数组公式（需按Ctrl+Shift+Enter确认），然后向右下拉填充到足够多的列：

=IFERROR(INDEX(FILTERXML("<x>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, " -", "|-"), " ", "</x><x>"), "|", "</x><x>")&"</x>", "//x[normalize-space()!='']"), COLUMN(A1)), "")

公式说明： #

1. 多层SUBSTITUTE：先将负数的负号与前面的空格替换为|-，再将所有空格替换为XML节点分隔符</x><x>，最后把|也替换为节点分隔符，构造出包含所有拆分部分的XML结构。
2. FILTERXML(..., "//x[normalize-space()!='']")：提取XML中的非空节点，得到拆分后的内容数组。
3. INDEX(..., COLUMN(A1))：依次取出数组中的每个元素，填充到对应列；IFERROR处理超出拆分数量的空值。

方案三：VBA自定义函数（兼容所有Excel版本） #

如果以上公式无法满足需求，可使用VBA编写自定义拆分函数，处理复杂场景更灵活：

1. 按Alt+F11打开VBA编辑器，插入新模块，粘贴以下代码：

Function SplitText(rng As Range) As Variant
    Dim inputStr As String, tempStr As String
    Dim resultArr() As String
    Dim i As Integer, isInNumber As Boolean, hasNegative As Boolean
    
    inputStr = Trim(rng.Value)
    tempStr = ""
    isInNumber = False
    hasNegative = False
    ReDim resultArr(0 To 0)
    
    For i = 1 To Len(inputStr)
        Dim char As String
        char = Mid(inputStr, i, 1)
        
        '识别负数开头的负号
        If char = "-" Then
            If Not isInNumber And (i = 1 Or Mid(inputStr, i - 1, 1) = " ") Then
                hasNegative = True
                tempStr = tempStr & char
                isInNumber = True
            Else
                tempStr = tempStr & char
            End If
        '识别数字字符
        ElseIf IsNumeric(char) Then
            tempStr = tempStr & char
            isInNumber = True
            hasNegative = False
        '处理非数字字符
        Else
            If isInNumber Then
                '结束数字部分，存入数组
                resultArr(UBound(resultArr)) = tempStr
                ReDim Preserve resultArr(UBound(resultArr) + 1)
                tempStr = ""
                isInNumber = False
            End If
            '处理空格，避免连续空格
            If char = " " Then
                If Len(tempStr) > 0 And Right(tempStr, 1) <> " " Then
                    tempStr = tempStr & " "
                End If
            Else
                tempStr = tempStr & char
            End If
        End If
    Next i
    
    '添加最后一个内容块
    If Trim(tempStr) <> "" Then
        resultArr(UBound(resultArr)) = Trim(tempStr)
    Else
        ReDim Preserve resultArr(UBound(resultArr) - 1)
    End If
    
    SplitText = resultArr
End Function

2. 返回Excel，在B1单元格输入=SplitText(A1)：
   • Office 365/2021：公式会自动溢出到右侧所有列；
   • 旧版Excel：需向右填充公式，用=INDEX(SplitText(A1), COLUMN(A1))逐个取出元素。

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