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

感谢您的任何回复。

- JohnMerlino

---

这是一个复杂的任务，因为有大量可能的街道后缀缩写 - 可以在这里查看列表。 - barrowc

Answer 1

在这种情况下，我会尝试忘记自己正在编程，并考虑我通常用什么思维过程来弄清楚每个东西。

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

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

希望这能帮到您。

- Mason

Answer 2

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

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

- Dick Kusleika

---

我最终使用了Excel公式。但还是谢谢你的回复。 - JohnMerlino

有两个问题。代码中有一个错误，它会给Len(vaStates(i))加上+1，但实际上不应该这样做。此外，当你向单元格写入类似数字的内容时，Excel会将其转换为数字。我编辑了+1并在单元格写入行之前添加了撇号，以保持它们为字符串。 - Dick Kusleika

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

- Charles Williams