我有以下格式的網址列:
120 Lemon Street Columbus OH 92738 (Basketball Courts)
我需要將其分為:街道地址(120 Lemon Street)
、城市(Columbus)
、州(OH)
、郵遞區號(92738)
和描述((Basketball Courts))
我有辦法做到這一點嗎?所有這些都處於相同的狀態,所以這不是問題。他們位於不同的城市/城鎮並具有不同的郵遞區號。
如果您只知道如何執行其中的一部分,請仍然告訴我。任何幫助表示讚賞,謝謝!
答案1
用戶1282637,我在下面設定了一個範例來向您展示。我只是將其作為一個起點,以便您可以以自己的完整答案結束。
首先,我設立了兩份清單。一個是所有州縮寫,另一個是我在這裡找到的所有公認的街道後綴:
http://pe.usps.com/text/pub28/28apc_002.htm
我使用列出的公式只是將列表轉換為以大寫字母開頭,然後其餘為小寫,就像您的書寫方式一樣。
接下來只是找出適用的內容。
這部分可以透過多種方式完成,但我決定採用這種方式作為範例。它只會在對應的行中列出一個數字,找到與所使用的後綴相符的數字。
我使用括號分隔(籃球場)部分:
我顯示剩餘的內容,因為在您的情況下,“籃球場”中的“法院”也是街道後綴:
接下來,我需要找到街道後綴的字串有多長,因此我使用以下內容:
和這個...
最後,這允許我只顯示街道:
現在我還沒有完成城市和州,但按照這個想法,你可以實現這一點。另外,如果你想要一個更乾淨的選擇,那麼一定要多學 VBA。我確實希望這至少能教您一些如何解決這個問題的想法。
答案2
User1282637 詢問是否有辦法完成此任務並請求任何協助。問題不在於 Excel 機制,而是如何處理資料中的歧義。解析郵遞區號和描述非常簡單(除非您混合使用 5 位數和 9 位數郵遞區號)。困難的問題是將街道與城市分開,所以我將重點放在這一點上。本文並非旨在逐步介紹如何使用 Excel 公式進行此操作。它只是分享對問題的一些見解,並描述一種獲得困難部分結果的方法。
問題是不同欄位之間沒有分隔符號。剝離描述或 ZIP 不是問題,因為它們很容易識別。問題是確定街道的終點和城市的起點。考慮街道部分的這些變化(遠非詳盡的清單):
120 Lemon Street
120 Lemon Drop Street
120 Lemon Street NW
120 East Lemon Street
120 Lemon Street Apt 3
街道中的「單字」數量可以從低至 1 或 2 個,到多達 7 或 8 個,因此這對於解析沒有用處。街道「類型」也不是特別有用。大約有 50-100 個單字用於街道「類型」(街道、大道、林蔭大道、道路、收費公路、車道、庭院、圓圈、露台等)。再加上街道類型縮寫(無論正確或錯誤),清單就有數百個。另外,這個名稱並不總是街頭領域的硬性規定。街道是最難識別的部分,因此邏輯方法是識別其餘部分,然後剩下的就是街道。
城市可以是幾個字。俄亥俄州華盛頓法院是三個字。然後考慮像俄亥俄州聖瑪麗斯這樣的情況。 「St」是城市名稱或街道類型名稱的一部分嗎?它屬於哪個領域?或者俄亥俄州南歐幾裡得——「南」是城市名稱的一部分還是街道地址的一部分?城市有它的問題,但有辦法解決它們。
即使使用郵遞區號來識別城市也存在問題。城市名稱和郵遞區號之間並不總是 1:1 匹配。
解決這個問題最實用的方法是使用「字典」:城市清單和郵遞區號目錄。這些是地址中最明確的部分。這些可以在網上或透過郵政服務找到。要進行比較,您可能需要清理資料或清單。它們需要相同的大寫樣式,並且資料中的任何額外空格都會阻止精確匹配。
如果您的資料或清單使用縮寫,您將需要處理它。當發現這些差異時,要么將未縮寫的縮寫翻譯為標準縮寫,要么根據縮寫詞典(也可以在線或從郵政服務獲取)進行二次匹配。
ZIP 很容易解析,這是一個很好的起點。對郵遞區號目錄進行郵遞區號查找。如果結果與緊鄰 ZIP 之前的字串完全匹配,則表示記錄的哪一部分是城市欄位。
如果沒有精確或明確的匹配,請前往比較城市名稱。迭代城市名稱列表。對於每個名稱,確定其包含的單字數,並將其與緊鄰 ZIP 之前的單字數進行比較。
如果您透過任一流程獲得匹配,則城市左側剩餘的所有內容都是街道地址。
使用資料庫應用程式比使用電子表格更容易完成此類應用程式。無論哪種方式,您都會發現嘗試以自動化方式完成此操作並不是一項簡單的任務。僅靠幾個電子表格公式是無法做到這一點的。
無論您的程式設計多麼嚴格,您可能仍有需要手動解析的記錄,以及需要手動修正的解析錯誤。您沒有表明您有多少筆記錄。簡單地手動完成可能會減少工作量。
如果數量很大而我不得不這樣做,我會剔除這個清單。符合簡單的記錄,例如明確的 ZIP 匹配。然後讓資料量決定您對自動匹配進行程式設計的程度。
對於留下的記錄,假設您已經剝離了 ZIP 和描述,這裡有一種加快手動過程的方法。查看記錄並直觀地識別城市中「單字」的數量,這是一項快速的腦力任務。在指定的儲存格中輸入該值,然後使用公式根據空格分隔符號的數量將街道與城市分開(在第 N 個空格處分隔,其中 N = 總空格數 + 1 - 城市名稱中的單字數)。
答案3
試試這個。如果您可以接受將地址和城市放在同一個單元格中,那麼這種簡單的方法應該非常有效。如果只有一個單詞,我有一個公式來提取城市,但如果有多個單詞的城市(例如紐約),它會變得更加複雜。
公式... 地址和城市:=LEFT(A2,FIND("OH",A2)-1) 州:=MID(A2,FIND("OH",A2),2) - 你提到一切都是OH,所以我保持簡單郵編:=MID(A2,FIND("OH",A2)+3,5) 說明:=TRIM(MID(A2,FIND("OH",A2)+8,30))
https://onedrive.live.com/redir?page=view&resid=D91C36B074F4D0F6!3224&authkey=!AO_MbW7Qxv4yWDo
答案4
代碼:
Sub SplitAddress()
Dim Addr As String
Dim l As Integer
Dim Desc As String
Dim Zip As String
Dim State As String
Dim City As String
Addr = Selection
l = InStrRev(Addr, "(")
Desc = Right(Addr, Len(Addr) - l + 1)
Addr = Left(Addr, InStrRev(Addr, " ", l) - 1)
l = InStrRev(Addr, " ")
Zip = Right(Addr, Len(Addr) - l)
Addr = Left(Addr, InStrRev(Addr, " ", l) - 1)
l = InStrRev(Addr, " ")
State = Right(Addr, Len(Addr) - l)
Addr = Left(Addr, InStrRev(Addr, " ", l) - 1)
l = InStrRev(Addr, " ")
City = Right(Addr, Len(Addr) - l)
Addr = Left(Addr, InStrRev(Addr, " ", l) - 1)
Selection.Offset(0, 1) = Addr
Selection.Offset(0, 2) = City
Selection.Offset(0, 3) = State
Selection.Range("B11").Offset(0, 4) = Zip
Selection.Range("B11").Offset(0, 5) = Desc
End Sub
說明:按下Alt+F11
上述程式碼並將其貼到出現的視窗中。然後選擇包含該位址的儲存格並返回到貼上程式碼的視窗並按F5
。
如果有效,我們可以努力使其更適合您的特定情況。