在Excel中使用VBA或公式来分隔街道名称和后缀？

Sub SplitStreetName()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim streetName As String
    Dim suffix As String
    
    ' 设置工作表和范围
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set rng = ws.Range("A1:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
    
    ' 遍历每个单元格
    For Each cell In rng
        streetName = cell.Value
        ' 查找空格位置
        If InStr(streetName, " ") > 0 Then
            suffix = Mid(streetName, InStrRev(streetName, " ") + 1)
            streetName = Left(streetName, InStrRev(streetName, " ") - 1)
            ' 将结果写入相邻单元格
            cell.Offset(0, 1).Value = streetName
            cell.Offset(0, 2).Value = suffix
        End If
    Next cell
End Sub

=LEFT(A2, FIND(" ", A2) - 1)

=RIGHT(A2, LEN(A2) - FIND(" ", A2))

If InStr(streetName, " ") > 0 Then
    ' 分隔街道名称和后缀
Else
    ' 处理没有空格的情况
End If

=LEFT(A2, FIND(" ", SUBSTITUTE(A2, " ", "X", LEN(A2) - LEN(SUBSTITUTE(A2, " ", "")) - 1)) - 1)