地址清洗是 Excel 数据处理里相当常见的一类需求省、市、区县、手机号、门牌号、邮编经常挤在同一个单元格里中间还夹杂公司名、括号、标点和各种不规则写法。用 LEFT、MID、FIND 组合公式去拆写一次勉强能用换一种地址格式公式就要重新调整数据一多整个表维护起来非常痛苦。这次我们来看一套基于 Excel VBA 正则表达式的地址信息提取方案。核心思路并不复杂用 VBScript.RegExp 正则对象去匹配省、市、区县、手机号、门牌号、座机、邮编再通过自定义函数和批量宏把结果写回表格。这套方案的优点很直接不依赖第三方插件Excel 和 WPS 都能用支持整列或全表批量处理提取函数可以作为单元格自定义函数直接调用几十行数据能跑几万行数据也能通过数组写入方式处理。本文会从开发环境准备开始给出可复制的 VBA 代码再用多组测试用例验证提取效果最后补充批量任务、外部调用、性能优化、常见问题和工程化建议。读者拿到代码后建议先在少量样本数据上验证一轮再放到全量数据上批量执行。1. Excel VBA 正则表达式提取地址信息核心能力速览能力项说明项目类型Excel/VBA 办公自动化脚本主要功能从混合地址文本中提取省份、城市、区县、门牌号、手机号、座机号、邮编、剩余详细地址支持平台Microsoft ExcelWPS 表格需启用 VBA 宏环境启动方式AltF8 运行宏自定义函数在单元格直接调用批量能力支持 A 列批量处理结果按行写回 B 到 G 列接口能力无标准 HTTP API但 VBA 自定义函数可作为单元格函数使用也可通过 PowerShell、Python xlwings 等 COM 方式外部调用核心依赖VBScript.RegExp 正则库采用后期绑定不需要手动勾选引用性能预期普通办公配置可处理大量数据推荐使用数组读写降低耗时适用场景订单地址清洗、会员地址补全、快递地址拆分、地址标准化预处理这套方案解决的核心问题只有一个把“一整块地址字符串”拆成“结构化字段”。它不会做语义纠错比如把“广洲”修正为“广州”也不会判断地址真伪。它的定位是预处理工具帮你把脏数据从 A 列变成 B、C、D 等字段后再交给后续流程。2. 适用场景与使用边界2.1 适合谁用物流行业处理订单时地址字段往往包含省市区、街道、门牌、联系方式用 VBA 正则可以先拆出省市区和电话方便后续分单。会员运营场景里用户填写的地址格式五花八门需要统一拆分入库。数据部门做地址清洗时也可以把这套函数作为基础清洗组件嵌入到模板工作簿中重复使用。只要数据是 Excel 表格、格式有一定规律、需要批量拆分这套方案就比逐条复制粘贴高效得多。2.2 不适合什么正则表达式是按“字符串结构”匹配的不是自然语言理解。以下情况不适合依赖正则地址里有大量错别字或同音字替代例如“深证市”地址缺少行政层级例如只有“科技园路 1 号”需要根据实际地理信息推断省市区需要处理数据库级别的地址标准化和空间落点。出现这些情况时建议先用人工或语义模型清洗一部分再用正则做结构化拆分。2.3 数据合规与隐私边界地址信息通常关联真实个人属于个人信息有些场景还涉及敏感个人信息。使用前要确认数据来源合法、用途合规。处理后的结果不要随意外发不要保存在公共网盘或聊天工具中。对外分享样例时需要先做脱敏处理把真实手机号、姓名、门牌号替换为测试数据。3. 环境准备与 VBA 正则的前置条件3.1 Excel 开发工具与宏开启在 Excel 中进入 VBA 编辑器的前提是显示“开发工具”选项卡。默认情况下Excel 2016 到 365 的“开发工具”是隐藏的。开启步骤打开 Excel点击“文件”进入“选项”点击“自定义功能区”在右侧主选项卡中勾选“开发工具”。如果要在工作簿中运行宏需要把文件保存为“启用宏的工作簿”即 .xlsm 格式。另存时选择“Excel 启用宏的工作簿 (*.xlsm)”即可。运行宏时还会遇到安全设置问题。默认情况下Excel 会禁用带宏的文件。第一次打开自己的 .xlsm 文件时文件上方会出现提示条点击“启用内容”即可。如果是公司电脑需要由管理员在“信任中心”中设置允许运行宏。3.2 WPS 表格的 VBA 环境WPS 表格自身对 VBA 的支持取决于版本。较新版本可能需要额外安装 VBA 宏插件部分版本默认不包含 VBA 功能。如果要在 WPS 中运行本文代码需要先确认电脑上的 WPS 支持 VBA或者已经安装匹配的 VBA 宏组件。安装第三方 VBA 插件时注意来源可信避免下载捆绑软件。3.3 打开 VBE 并插入模块在 Excel 或 WPS 中点击“开发工具”选项卡下的“Visual Basic”按钮或直接使用快捷键 AltF11 进入 VBE 界面。在左侧工程资源管理器中右键当前工作簿名称选择“插入”-“模块”。把代码粘贴到模块中保存后即可使用。3.4 正则库的两种引用方式Excel VBA 中调用正则有两种方式前期绑定和后期绑定。前期绑定需要先在 VBE 菜单“工具”-“引用”中勾选“Microsoft VBScript Regular Expressions 5.5”然后代码中可以用New RegExp。后期绑定不依赖引用设置直接使用CreateObject(VBScript.RegExp)。推荐使用后期绑定原因是换电脑、换 Office 版本、拷贝到 WPS 时不需要重新勾选引用代码兼容性更好。本文所有代码都采用后期绑定。 后期绑定写法 Dim re As Object Set re CreateObject(VBScript.RegExp)4. VBA 正则表达式语法入门VBA 使用的正则引擎是 VBScript.RegExp它与 Python、JavaScript 的正则写法相近但有几个差异需要注意。4.1 RegExp 对象的基本属性属性作用常用值Pattern设置正则表达式模式字符串Global是否匹配所有结果True 匹配全部False 只匹配第一处IgnoreCase是否忽略大小写True 忽略大小写Execute方法返回匹配集合MatchCollection每个匹配项都有Value、FirstIndex、Length属性。带括号的分组匹配结果可以通过SubMatches访问。4.2 常用的元字符Excel VBA 正则中常用的元字符如下字符说明\d匹配数字\w匹配字母、数字、下划线\s匹配空白字符.匹配除换行外的任意字符^匹配字符串开头$匹配字符串结尾[]字符集合匹配其中一个字符[^]排除字符集合()捕获分组|或匹配{m,n}匹配 m 到 n 次*匹配 0 次或多次匹配 1 次或多次?匹配 0 次或 1 次4.3 VBA 正则匹配中文网上很多教程用[\u4e00-\u9fa5]匹配中文这在 Python 中没问题但在 VBA 的 VBScript.RegExp 中是不生效的因为该引擎不识别\u转义。推荐直接写中文字符范围 匹配连续中文 [一-龥]这个范围覆盖了常用汉字在 VBE 代码窗口中可以正常保存和执行。提取地址时配合排除字符类可以起到类似“非贪婪”的控制效果。4.4 贪婪匹配与模拟非贪婪VBScript.RegExp 默认是贪婪匹配而且不支持*?、?这类非贪婪写法。举个例子模式.*市在文本“江苏省南京市鼓楼区”中会尽量匹配到最后一个“市”之前的所有内容大概率不是想要的结果。解决思路是用排除字符类模拟非贪婪[^市]{2,15}市表示“匹配 2 到 15 个不含‘市’的字符后面紧跟一个‘市’”。这样在“江苏省南京市鼓楼区”中会匹配“南京市”而不是整串。这套方法在提取省、市、区县时很实用。5. Excel VBA 地址信息提取代码实现下面给出完整代码。所有函数都放在一个标准模块中即可。5.1 基础匹配函数 获取第一处匹配的完整字符串 Private Function MatchFirst(ByVal txt As String, ByVal pattern As String) As String Dim re As Object Set re CreateObject(VBScript.RegExp) re.Pattern pattern re.Global False re.IgnoreCase True Dim m As Object Set m re.Execute(txt) If m.Count 0 Then MatchFirst m(0).Value End If End Function 获取第一处匹配中的指定分组内容 Private Function MatchSubFirst(ByVal txt As String, ByVal pattern As String, ByVal groupIndex As Long) As String Dim re As Object Set re CreateObject(VBScript.RegExp) re.Pattern pattern re.Global False re.IgnoreCase True Dim m As Object Set m re.Execute(txt) If m.Count 0 Then If groupIndex m(0).SubMatches.Count Then MatchSubFirst m(0).SubMatches(groupIndex) End If End If End Function 移除字符串中第一次出现的指定子串 Private Function RemoveFirstMatch(ByVal source As String, ByVal matchText As String) As String If Len(matchText) 0 Then RemoveFirstMatch source Exit Function End If Dim pos As Long pos InStr(1, source, matchText, vbBinaryCompare) If pos 0 Then RemoveFirstMatch Left(source, pos - 1) Mid(source, pos Len(matchText)) Else RemoveFirstMatch source End If End Function5.2 提取省份省份的匹配关键点是“省”“自治区”“特别行政区”三类行政区划单位。 提取省份 Function ExtractProvince(ByVal txt As String) As String Dim s As String s MatchFirst(txt, [^省]{2,15}(省|自治区|特别行政区)) ExtractProvince Trim(s) End Function这个模式有两个要点[^省]{2,15}表示匹配 2 到 15 个不含“省”的字符(省|自治区|特别行政区)表示后续必须是行政区划后缀。“内蒙古自治区”这类写法也能匹配因为“自治区”中不含“省”。5.3 提取城市提取城市前先移除省份避免“省”干扰后续匹配。 提取城市 Function ExtractCity(ByVal txt As String) As String Dim province As String province ExtractProvince(txt) Dim rest As String rest RemoveFirstMatch(txt, province) Dim s As String s MatchFirst(rest, [^市州盟]{2,15}(市|自治州|地区|盟)) ExtractCity Trim(s) End Function这里匹配市级单位时把“市、自治州、地区、盟”都作为可选后缀覆盖更广的行政地名。5.4 提取区县提取完省市后再移除市级名称然后在剩余文本中匹配区县级单位。 提取区县 Function ExtractDistrict(ByVal txt As String) As String Dim province As String province ExtractProvince(txt) Dim rest As String rest RemoveFirstMatch(txt, province) Dim city As String city ExtractCity(txt) rest RemoveFirstMatch(rest, city) Dim s As String s MatchFirst(rest, [^区县旗]{2,15}(区|县|旗)) ExtractDistrict Trim(s) End Function例如“广东省深圳市南山区科技园路 1 号”先提取“广东省”再提取“深圳市”最后从剩余文本中提取“南山区”。5.5 提取手机号、座机号、邮编、门牌号手机号规则是 11 位数字以 1 开头第二位通常是 3 到 9。 提取手机号 Function ExtractMobile(ByVal txt As String) As String ExtractMobile MatchFirst(txt, 1[3-9]\d{9}) End Function 提取座机号 Function ExtractPhone(ByVal txt As String) As String ExtractPhone MatchFirst(txt, 0\d{2,3}-?\d{7,8}) End Function 提取邮编优先提取“邮编:123456”这样的写法 Function ExtractPostcode(ByVal txt As String) As String Dim s As String s MatchSubFirst(txt, 邮编[:\s]*(\d{6}), 0) If s Then s MatchFirst(txt, \d{6}) End If ExtractPostcode Trim(s) End Function 提取门牌号 Function ExtractHouseNumber(ByVal txt As String) As String ExtractHouseNumber MatchFirst(txt, [\d一二三四五六七八九十]{1,8}号) End Function邮编的匹配容易出现误判因为地址中 6 位连续数字可能出现在门牌号或编号中。如果单元格里包含“邮编100020”这类写法优先用带关键词的规则。5.6 提取剩余详细地址详细地址是移除省市区、电话、邮编、门牌号之后剩下的街道、楼栋、公司名等信息。 提取剩余详细地址 Function ExtractDetail(ByVal txt As String) As String Dim rest As String rest txt rest RemoveFirstMatch(rest, ExtractProvince(txt)) rest RemoveFirstMatch(rest, ExtractCity(txt)) rest RemoveFirstMatch(rest, ExtractDistrict(txt)) rest Replace(rest, ExtractMobile(txt), ) rest Replace(rest, ExtractPhone(txt), ) rest Replace(rest, ExtractHouseNumber(txt), ) rest Replace(rest, ExtractPostcode(txt), ) rest CleanAddress(rest) ExtractDetail rest End Function 清理地址中的多余标点和空格 Private Function CleanAddress(ByVal s As String) As String s Replace(s, , ) s Replace(s, ,, ) s Replace(s, 。, ) s Replace(s, , ) s Replace(s, ;, ) s Replace(s, Chr(32), ) s Replace(s, Chr(9), ) Do While InStr(s, ) 0 s Replace(s, , ) Loop CleanAddress Trim(s) End Function5.7 批量提取主流程批量任务建议使用“数组读取 数组写回”的方式避免逐单元格读写带来的性能损耗。 批量提取A2 开始读取结果写入 B 到 G 列 Sub BatchExtractAddress() Application.ScreenUpdating False Dim ws As Worksheet Set ws ThisWorkbook.Sheets(Sheet1) Dim lastRow As Long lastRow ws.Cells(ws.Rows.Count, 1).End(xlUp).Row If lastRow 2 Then Exit Sub Dim srcData As Variant srcData ws.Range(A2:A lastRow).Value Dim