在 Excel 中拆分地址、城市、州和邮政编码，缺少分隔符

我有一个 Excel 电子表格，其中包含打包在单个单元格中的整个地址，没有分隔符。地址如下所示：

2701 NW 64TH TER MARGATE FL 33063-1703 
901 NE 8 ST HALLANDALE BEACH FL 33009-2626 
1840 DEWEY ST UNIT 305 HOLLYWOOD FL 33020 
3049 NE 4 AVE WILTON MANORS FL 33334-2047
650 NE 56 CT OAKLAND PARK FL 33334-3528 

因此，A 列中的前五个单元格将包含上述地址。

正如您所看到的，某些城市由两个单词组成，但州始终是 FL 或 NY。我所需要做的就是将地址、城市、州和邮政编码分隔在各自的列中。我希望有一种方法可以在 Excel 中的 VBD（Visual Basic for Developers）中执行此操作。所以我可以把它放入宏中。

我知道如何做到这一点，但我的VBD是有限的：

stateArray = Split("FL, NY")
cityArray = Split("Fort Lauderdale","Sunrise","Oakland Park")

例如，使用另一种编程语言，您可能会执行以下操作：

var arrStates, arrCities
arrCities = ["Fort Lauderdale", "Sunrise", "Oakland Park"]
arrStates = ["FL", "NY"]

var findAddress = function(curCity, curState){
    for(var i=0; i < arrCities.length; i < arrStates.length; i--){

        (arrCities[i] == curCity) ? arrCities[i] = CurCity : arrCities[i] = null;
        (arrStates[i] == curState) ? arrStates[i] = curState : arrStates[i] = null;

    }   

    if(arrCities[i] >= 0){
        var city = arrCities[i];
    }

    if(arrStates[i] >= 0){
        var state = arrStates[i];
    }

    createTable(city, state);

}

var createTable = function(city, state){
    var tbl = document.createElement("Table");
    var newRow = document.createElement("tr");
    tbl.appendChild(newRow);
    cols = [city, state];

    for(var i=0; i < cols.length; i++){
        var newCol = document.createElement("td");
        newCol.innerText = cols[i];
        newRow.appendChild(newCol);
    }
}

感谢您的任何回复。

看来，如果您必须输入所有城市，您不妨手动拆分所有单元格。识别所有街道类型并将其用作分隔符可能会更容易。请注意数组中字符串周围的空格。

Sub SplitAddresses()

    Dim vaStates As Variant
    Dim vaStreets As Variant
    Dim i As Long
    Dim rCell As Range
    Dim sAddress As String
    Dim sCity As String, sState As String
    Dim sZip As String
    Dim lStreetPos As Long, lStatePos As Long

    vaStates = Array(" FL ", " NY ")
    vaStreets = Array(" TER ", " ST ", " AVE ", " CT ")

    For Each rCell In Sheet1.Range("A1:A5").Cells
        sAddress = "": sCity = "": sZip = "": sState = ""
        For i = LBound(vaStreets) To UBound(vaStreets)
            lStreetPos = InStr(1, rCell.Value, vaStreets(i))
            If lStreetPos > 0 Then
                sAddress = Trim(Left$(rCell.Value, lStreetPos + Len(vaStreets(i)) - 1))
                Exit For
            End If
        Next i

        For i = LBound(vaStates) To UBound(vaStates)
            lStatePos = InStr(1, rCell.Value, vaStates(i))
            If lStatePos > 0 Then
                sCity = Trim(Mid$(rCell.Value, Len(sAddress) + 1, lStatePos - Len(sAddress) - 1))
                sState = Trim(Mid$(rCell.Value, lStatePos + 1, Len(vaStates(i)) - 1))
                sZip = Trim(Mid$(rCell.Value, lStatePos + Len(vaStates(i)), Len(rCell.Value)))
                Exit For
            End If
        Next i

        rCell.Offset(0, 1).Value = "'" & sAddress
        rCell.Offset(0, 2).Value = "'" & sCity
        rCell.Offset(0, 3).Value = "'" & sState
        rCell.Offset(0, 4).Value = "'" & sZip

    Next rCell

End Sub