如何在Excel中從地址列表提取郵遞區號？

Author Sun Last modified 2025-08-23

在Excel中管理客戶資訊時，通常會有一份完整的地址清單，每個條目都包含街道詳細資訊和郵遞區號（郵政編碼）。如果需要從這些地址中單獨提取郵遞區號，可能是為了郵寄列表、區域分析或數據格式化，手動逐一分離它們可能會非常耗時且容易出錯，特別是在處理大量數據時。幸運的是，Excel 提供了實用的方法，可以快速高效地一次性提取多個郵遞區號，而無需手動操作。本文提供了使用Excel公式和VBA宏解決方案從地址中提取郵遞區號的逐步指導。

使用公式在Excel中提取郵遞區號

使用用戶自定義函數在Excel中提取郵遞區號

使用公式在Excel中提取郵遞區號

在許多常見的地址列表中，郵遞區號位於地址字串的末尾，並且不超過8個字符。當地址列表中的所有地址都遵循一致的格式，且郵遞區號作為每個地址的最後一個元素並以空格分隔時，這種方法最為適用。

要使用公式快速提取郵遞區號，請按照以下步驟操作：

1. 選擇一個空白單元格，您希望郵遞區號顯示在該位置（例如，若您的地址在A1，則選擇B1）。輸入以下公式：

=MID(A1,FIND("zzz",SUBSTITUTE(A1," ","zzz",SUMPRODUCT(1*((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))=" "))-1))+1,LEN(A1))

2. 按下Enter鍵。A1中的地址郵遞區號將顯示在選定的單元格中。

3. 要將此公式應用於其他地址，選擇帶有公式的單元格，沿著列向下拖動填充柄以覆蓋所有地址行，Excel將自動為每個地址提取郵遞區號。

a screenshot of extracting postcode from address list with formula

提示：此公式假設郵遞區號是地址字串中最後一個空格後的最後一項。如果您的地址結構不同，例如郵遞區號出現在中間或以逗號分隔，此公式可能無法返回正確結果。要將此方法擴展到國際郵遞區號或自定義格式，您可能需要不同的或更進階的公式方法，或者嘗試下面的VBA解決方案。

使用用戶自定義函數在Excel中提取郵遞區號

如果您的地址數據更加複雜、不一致，或郵遞區號以各種格式和位置出現在字串中，如下方截圖所示，VBA宏可以提供更靈活的提取選項。此自動化方法快速處理整個地址列，僅提取和返回郵遞區號，大大節省時間並減少手動操作。

extract various postcodes from address list

1. 按Alt + F11打開Microsoft Visual Basic for Applications窗口。

2. 在VBA窗口中，點擊插入 > 模組以創建新模組。將以下VBA代碼複製並粘貼到模組窗口中：

Public Function ExtractPostcode(text As String) As String
    Dim reg As New RegExp
    Dim m As MatchCollection
    reg.Pattern = "\b([A-Z]{1,2}\d{1,2}[A-Z]?\s*\d[A-Z]{2}|\d{5}(?:-\d{4})?|\d{6})\b"
    reg.IgnoreCase = True
    reg.Global = False
    
    If reg.Test(text) Then
        Set m = reg.Execute(text)
        ExtractPostcode = m(0).Value
    Else
        ExtractPostcode = ""
    End If
End Function

3. 粘貼代碼後，在VBA編輯器窗口中，選擇工具 > 引用。見截圖：

click Tools > References

4. 在引用對話框中，勾選Microsoft VBScript Regular Expressions 5.5並點擊確定。

check Microsoft VBScript Regular Expressions 5.5

5. 返回工作表，並輸入以下公式：=ExtractPostcode(A2)，向下拖動填充柄至其他單元格。所有郵遞區號立即顯示，見截圖：

tye a formula to get the result

提示：通過此代碼，您可以自動在幾秒內從任何國家或地區的Excel數據中提取郵遞區號；只需簡單調整正則表達式以匹配目標區域的郵遞區號規則，即可快速適應不同格式——例如英國的“SW1A 1AA”、美國的“12345-6789”或中國的“100000”——大幅提升數據清理和分析效率。

最佳 Office 生產力工具

🤖	Kutools AI 助手：以智能執行為基礎，革新數據分析｜生成程式碼｜創建自訂公式｜分析數據並生成圖表｜調用 Kutools 增強函數…
	熱門功能：查找、選取項目的背景色或標記重複值｜刪除空行｜合併列或單元格且不遺失數據｜四捨五入（免公式）...
	高級 LOOKUP：多條件 VLookup｜多值 VLookup｜多表查找｜模糊查找...
	高級下拉列表：快速創建下拉列表｜依賴型下拉列表｜多選下拉列表...
	列管理器：添加指定數量的列｜移動列｜切換隱藏列的顯示狀態｜比較區域及列...
	精選功能：網格聚焦｜設計檢視｜增強編輯欄｜工作簿及工作表管理器｜資源庫（快捷文本）｜日期提取器｜合併資料｜加密/解密儲存格｜按列表發送電子郵件｜超級篩選｜特殊篩選（篩選粗體/傾斜/刪除線...）...
	15 大工具集：12 項文本工具（添加文本、刪除特定字符…）｜50+ 儀表類型（甘特圖等）｜40+ 實用公式（基於生日計算年齡等）｜19 項插入工具（插入QR码、根據路徑插入圖片等）｜12 項轉換工具（金額轉大寫、匯率轉換等）｜7 項合併與分割工具（高級合併行、分割儲存格等）｜...及更多