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

Question

我有一个 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);
    }
}

感谢您的任何回复。

Answer 1

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

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

Answer 2

在这种情况下，我会尝试忘记我正在编程，而只是想想我会使用什么样的思维过程来弄清楚每个过程通常是什么。

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 

乍一看，事情可能看起来很忙碌，但如果你仔细观察，就会发现其中的规律。

1. 地址均以一组 # 开头，作为街道地址的开头
2. 街道地址始终以街道类型缩写结尾
3. 如果有建筑物单元，则位于街道地址之后
4. 地址均以邮政编码结尾
5. 之前邮政编码是州的缩写，
6. 城市名称正好位于中间，如果其他所有内容都被剥离的话，就可以了。

这使得模式如下：

Street # : Street Type : Unit {Optional} : City : State: Zip Code

使用字符串函数从临时字符串变量中剥离每个部分，您应该能够相当轻松地重建它。

希望有帮助。

Answer 3

以下是一些帮助您入门的 VBA 代码：您需要添加错误处理

Option Explicit
Option Compare Text
Sub SplitAddress()
    Dim vStates As Variant
    Dim vCities As Variant
    Dim vInput As Variant
    Dim vAddress() As Variant
    Dim j As Long
    Dim str1 As String

    ' States/Cities/Inputs are named ranges containing the data
    vStates = [States]
    vCities = [Cities]
    vInput = [Inputs]

    ReDim vAddress(1 To UBound(vInput) - LBound(vInput) + 1, 1 To 4)
    For j = 1 To UBound(vInput)
        str1 = Trim(CStr(vInput(j, 1)))
        If Len(str1) = 0 Then Exit For
        FindSplit j, 3, str1, vStates, vAddress()
        FindSplit j, 2, str1, vCities, vAddress()
    Next j

    ActiveSheet.Range("A2").Resize(UBound(vAddress), UBound(vAddress, 2)) = vAddress
End Sub
Sub FindSplit(j As Long, k As Long, str1 As String, vItems As Variant, vAddress() As Variant)
    Dim iPos As Long
    Dim jItem As Long
    Dim strItem As String

    For jItem = 1 To UBound(vItems)
        strItem = Trim(CStr(vItems(jItem, 1)))
        iPos = InStr(str1, " " & strItem & " ")
        If iPos > 0 Then
            vAddress(j, k) = Mid(str1, iPos + 1, Len(strItem))
            If k = 3 Then
                vAddress(j, k + 1) = Right(str1, Len(str1) - (iPos + 3))
                str1 = Left(str1, iPos)
            Else
                vAddress(j, 1) = Left(str1, iPos - 1)
            End If
            Exit For
        End If
    Next jItem
End Sub