分析第一个数字的左侧

Sub ParseLeftOfDigit()
    
    Dim Data(): Data = Sheet1.Range("A2:A5000").Value
    
    Dim r As Long, n As Long, rStr As String
    
    For r = 1 To UBound(Data, 1)
        rStr = CStr(Data(r, 1))
        For n = 1 To Len(rStr)
            If Mid(rStr, n, 1) Like "#" Then
                Data(r, 1) = Mid(rStr, 1, n - 1)
                Exit For
            End If
        Next n
    Next r
    
    Sheet3.Range("B2:B5000").Value = Data

End Sub

此宏将把该公式应用于您希望的范围。

Sub Filter_Text_Formula()
Sheets("Sheet3").Range("B2:B5000").FormulaR1C1 = "=LEFT(Sheet1!RC[-1],MIN(FIND({1,2,3,4,5,6,7,8,9,0},Sheet1!RC[-1] & ""1234567890""))-1)"
End Sub