在Excel中拆分地址，城市，州和邮政编码，缺少分隔符 [英] Splitting address, city, state, and zip, lacking delimiters, in excel

问题描述

我有一个excel电子表格，其中包含打包在单个单元格中的所有地址，没有分隔符.地址看起来像这样:

I have an excel spreadsheet that contains entire addresses packed in a single cell without delimiters. The addresses look like this:

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的前五个单元格将包含上述地址.

So the first five cells in column A would contain the above addresses.

如您所见，有些城市由两个词组成，但州始终为FL或NY.我需要做的就是将地址，城市，州和邮编分别放在自己的列中.我希望有一种方法可以在excel的VBD(开发人员的Visual Basic)中进行.因此，我可以将其放入宏中.

As you can see, some of the cities consist of two words but the state is always FL or NY. All I need to do is separate the address, city, state, and zip in their own columns. I'm hoping there's a way to do this in VBD (Visual Basic for Developers) in excel. So I can put it into a macro.

我对如何实现有一个想法，但是我的VBD受到限制:

I have an idea of how it can be done, but my VBD is limited:

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

例如，另一种编程语言可能会执行以下操作:

For example, another programming language you might do something like this:

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);
    }
}

感谢您的任何回应.

推荐答案

似乎，如果必须键入所有城市，则最好手动拆分所有单元格.识别所有街道类型并将其用作定界符可能会更容易.请注意数组中字符串周围的空格.

It seems that if you have to type out all the cities, you might as well just split all the cells manually. It may be easier to identify all the street types and use that as a delimiter. Note the spaces around the strings in the array.

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

这篇关于在Excel中拆分地址，城市，州和邮政编码，缺少分隔符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！