看看这是否适合你：

11月30日更新日期：

Sub test()

    Dim RegEx As Object
    Dim strTest As String
    Dim ThisCell As Range
    Dim Matches As Object
    Dim strNumber As String
    Dim strText As String
    Dim i As Integer 
    Dim CurrCol As Integer

    Set RegEx = CreateObject("VBScript.RegExp")
    ' may need to be tweaked
    RegEx.Pattern = "-?\d+"

    ' Get the current column
    CurrCol = ActiveCell.Column

    Dim lngLastRow As Long
    lngLastRow = Cells(1, CurrCol).End(xlDown).Row

    ' add a new column & shift column 2 to the right
    Columns(CurrCol + 1).Insert Shift:=xlToRight

    For i = 1 To lngLastRow  ' change to number of rows to search
        Set ThisCell = ActiveSheet.Cells(i, CurrCol)
        strTest = ThisCell.Value
        If RegEx.test(strTest) Then
            Set Matches = RegEx.Execute(strTest)
            strNumber = CStr(Matches(0))
            strText = Mid(strTest, Len(strNumber) + 1)
            ' replace original cell with number only portion
            ThisCell.Value = strNumber
            ' replace cell to the right with string portion
            ThisCell.Offset(0, 1).Value = strText
        End If
    Next

    Set RegEx = Nothing
End Sub

不如这样：

Sub UpdateCells()
Dim rng As Range
Dim c As Range
Dim l As Long
Dim s As String, a As String, b As String

''Working with sheet1 and column C
With Sheet1
    l = .Range("C" & .Rows.Count).End(xlUp).Row
    Set rng = .Range("C1:C" & l)
End With

''Working with selected range from above
For Each c In rng.Cells
    If c <> vbNullString Then
        s = FirstNonNumeric(c.Value)

        ''Split the string into numeric and non-numeric, based
        ''on the position of first non-numeric, obtained above. 
        a = Mid(c.Value, 1, InStr(c.Value, s) - 1)
        b = Mid(c.Value, InStr(c.Value, s))

        ''Put the two values on the sheet in positions one and two 
        ''columns further along than the test column. The offset 
        ''can be any suitable value.
        c.Offset(0, 1) = a
        c.Offset(0, 2) = b
    End If
Next
End Sub

Function FirstNonNumeric(txt As String) As String
    With CreateObject("VBScript.RegExp")
        .Pattern = "[^0-9]"
        FirstNonNumeric = .Execute(txt)(0)
    End With
End Function

您可以使用Excel VBA通过以下步骤将字符串与数字分开：
编写下面的代码：
vbnet复制代码Sub SeparateStringsFromNumbers（）作为范围，作为范围的单元格Dim str作为字符串，num作为字符串

Set rng = Selection 'select the range you want to separate

For Each cell In rng
    str = ""
    num = ""
    
    For i = 1 To Len(cell)
        If IsNumeric(Mid(cell, i, 1)) Then
            num = num & Mid(cell, i, 1)
        Else
            str = str & Mid(cell, i, 1)
        End If
    Next i
    
    cell.Offset(0, 1) = str 'output the string portion to the cell next to it
    cell.Value = num 'replace the original cell with the numeric portion
Next cell

末端子组件
按"F5"或单击"运行"〉"运行Sub/UserForm"执行代码。
选择要将字符串与数字分隔开的单元格范围，然后单击"确定"。
然后，宏将字符串与数字分离，并将字符串部分输出到它旁边的单元格，并用数字部分替换原始单元格。
注意：此代码假定数据位于单列中。如果数据位于多列中，则需要相应地修改代码。