表格治理艺术:WPS正则表达式函数REGEXP助力数据清洗实战
优秀创作者
全文约2500字;
阅读时间:约7分钟;
听完时间:约14分钟;
在整理员工基本信息档案时,工厂的人事专员遇到了一个棘手的问题:某一列数据混合记载了员工的身份证号码、银行卡号、开户行信息及手机号码,且这些信息被无序地集中在一个单元格内,格式极不规范。具体而言,身份证号码间夹杂着空格,部分甚至前端附有不可见字符,还有些数据前带有文本标识符“’”。
为应对这一挑战,领导指示专员需将这些混杂的信息拆分并各自独立成列,即分别列出身份证号、银行卡号、手机号和开户行等。然而,鉴于当前数据格式的不一致性与混乱状况,手动完成此项任务极为困难且耗时。因此,迫切需要设计或采用一种自动化表格处理函数来有效解决这一问题,确保信息的准确分离与归类。
解决思路
尽管面临的数据问题繁多,但经过仔细观察,我们发现这些数据仍存在一定规律可循。特别是身份证号、银行卡号以及手机号均以数字为主,只是其间穿插了一些不规则字符。针对这一特点,我们可以利用WPS表格中的替换函数,清除这些不必要的字符,初步净化数据。净化完成后,即可着手将这些纯数字信息从原单元格中分离,各自置于新的列中。
在进行数据分类时,我们可以依据常见的编号规则作为指引:身份证号码固定为18位,银行卡号多为16、17或19位,手机号码则一般为11位。依据这些长度特征,我们可以较为准确地辨识并区分出不同类型的数字串代表的意义。
至于开户行信息的提取,则更多依赖于文本的识别。一个简便的方法是查找以“中”字起始、以“行”字结束的文本段落,这通常能帮助我们定位到开户行信息。通过这样的规则匹配,即使在原始数据格式不统一的情况下,也能有效地完成信息的分类与整理工作。
分离数字
为了帮助大家更清晰地理解函数的应用过程,我将分步骤介绍函数的使用方法,并说明如何在合适的位置输入函数公式并进行填充。以下是一个示例公式:
=REGEXP(B3,"\d.+")
函数解释:
\d: 这是一个特殊字符序列,表示匹配任何数字(0-9)。
.+: 这里的 . 表示匹配任何单个字符(除了换行符),而 + 表示匹配前面的字符一次或多次。
因此,整个正则表达式 "\d.+" 将会匹配任何以数字开头的文本,并且会继续匹配该数字之后的所有字符,直到遇到一个换行符
替换空格
在成功提取出数字后,我们注意到部分数字间包含空格,需要进一步清理这些空格以确保数据准确性。接下来,通过运用替换函数达到去空格的目的。请在合适的数据范围内输入并填充以下公式:
=SUBSTITUTE(REGEXP(B3,"\d.+")," ","")
函数解释:
利用SUBSTITUTE函数,将从REGEXP得到的结果中所有的空格(" ")替换为无("")(即移除空格)。
信息判断
把上面的结果定义为A,,接着,在水平方向上,于C2至E2单元格分别填入标题{"身份证", "银行卡", "手机"}。依据既定规则——身份证号码为18位,银行卡号常见为16、17或19位,手机号码通常是11位——我们将在相应位置应用以下公式并向下填充以匹配每一条记录:
=IFNA(LET(A,SUBSTITUTE(REGEXP($B3,"\d.+")," ",""),INDEX(A,,MATCH(C$2,XLOOKUP(LEN(A),{11;16;17;18;19},{"手机";"银行卡";"银行卡";"身份证";"银行卡"}),0))),0)
函数解释:
IFNA: 这个函数用于处理可能出现的错误值 #N/A(未找到匹配项时的情况)。如果公式内的计算返回了 #N/A 错误,IFNA 会替代为指定的值,这里是 0。
LET: 用于定义并命名计算过程中的临时变量,提高公式的可读性和效率。这里定义了一个变量 A。
MATCH(C$2,XLOOKUP(...,...,...),0):C$2: 指定的标题,比如 "身份证"、"银行卡" 或 "手机"。
XLOOKUP(LEN(A),{11;16;17;18;19},{"手机";"银行卡";"银行卡";"身份证";"银行卡"}):
LEN(A): 计算数组 A 中每个数字序列的长度。{11;16;17;18;19}: 预定义的一系列长度,对应不同类型的号码。{"手机";"银行卡";"银行卡";"身份证";"银行卡"}: 对应长度的类型标签。 注意这里的配置可能需要根据实际情况调整,因为银行卡号长度的重复可能导致匹配逻辑不够精确。可以灵活的增加预设值
XLOOKUP 根据数字的长度在上述列表中查找,并返回对应的类型标签(如 "手机")。
MATCH(...,0): 使用 MATCH 函数找到类型标签在数组 {"手机";"银行卡";"身份证"} 中的位置,第三个参数 0 表示完全匹配。
INDEX(A,,...): 根据 MATCH 找到的位置,从数组 A 中取出对应的值。第二个逗号后留空表示取整行,而实际位置由 MATCH 决定。
综上所述,整个公式旨在根据数字的长度自动将其分类并对应到“身份证”、“银行卡”或“手机”等标题下,同时处理可能出现的错误情况,确保输出结果的准确性。
提取银行
提取银行信息的任务确实可能涉及复杂性,尤其是在格式不一的数据中。若假设所有银行名称都以“*行”作为结尾标识,且该“行”字符后可能跟随其他信息或直接结束。录入以下公式:
=REGEXP(B3,"[ \n\r]+.*?行(?:[^\n\r]*|$)[ \n\r]*")
公式解释:
这个正则表达式的各部分解释如下:
[ \n\r]+:开始前匹配一个或多个空格、换行符或回车符。
.*?行:非贪婪地匹配任意字符直到遇到“行”字。
(?:[^\n\r]*|$):这是一个非捕获组,表示两种可能:
[^\n\r]*:匹配任意数量的非换行符字符,意味着“行”后面可以跟任意文本。
|:或
$:直接到字符串结束,意味着“行”是字符串的结尾。
[ \n\r]*:最后匹配任意数量的空格、换行符或回车符,以适应文本末尾可能存在的空白字符。
这样,无论是文本1中的“中国工商银行西安金花南路支行”还是文本2中的“建设银行醴陵支行”,都能被正确提取出来,不论“行”字后面是否有额外的字符。
最后总结:
通过上述步骤,我们成功地展示了如何在面对复杂且不规范的数据格式时,运用一系列精心设计的公式与逻辑推理,将混杂在单一单元格内的员工信息精准分离并归类。从最初的识别数字序列、去除不必要的空格,到依据特征长度区分身份证号、银行卡号与手机号,再到通过正则表达式巧妙提取开户行信息,这一系列操作不仅体现了数据分析的巧思,也彰显了技术在解决实际问题中的强大效能。
特别地,对于开户行信息的提取,我们定制的正则表达式策略,充分考虑了文本多样性的挑战,实现了无论“行”字符后是否接续其他信息,都能准确捕获银行名称的目标。这一过程不仅是对数据处理技能的实践,也是对问题解决思路灵活性的考验。
总结而言,本案例不仅解决了工厂人事专员面临的棘手问题,还为处理类似数据分离与清洗任务提供了宝贵的参考范例。它证明了,即便在数据格式不尽人意的情况下,结合适当的工具与方法论,依然能够高效地挖掘数据价值,提升信息管理的规范性和效率。这种结合观察、分析、实施的解决路径,对于任何需要处理大量数据的组织或个人来说,都是一笔宝贵的财富,强调了在数字化时代掌握高级数据处理技巧的重要性。
384 WPS表格时间秘籍:用REGEXP函数玩转小时与分钟的转换艺术
380 四维到二维,重塑生产数据:SCAN函数合并单元格处理实战
377 数据重塑术:PIVOTBY函数打造PMC智慧行动的销售分析蓝图
375 销售预测新视角:WPS表格中的三大策略优化S&OP流程
373 精密PMC调度的艺术:Takt Time, 循环时间与线平衡率深度解析
372 智破表格障碍:XLOOKUP助力PMC精准对接销售需求MDS
369 深度解析:如何优雅处理WPS表格“0”,保持数据清晰度
368 透视仓储运营,BYCOL、IFERROR、N函数引领高效数据智慧
366 XMATCH函数解锁销售数据的秘密:精准定位最小有效销量
365 【仓储智慧】一维与二维表的较量:打造高效物料管控体系
364 解锁S&OP秘籍:动态同比分析,精准驾驭市场波动
363 从数据分析到行动指南:优化S&OP流程,破解季节性销售谜题
362 WPS表格智能规划,避免断供:MDS系统在库存管理中的应用实践
361 规格乱序不再愁,TEXTSPLIT函数高效解码BOM标准化
360 办公效率升级!WPS函数实战攻略:解析身份证,一键生成查询表
359 职场必备!双剑合璧:SUMIFS函数与数据透视表应用
358 破局WPS WRAPROWS限制:非标一维数据到二维表的灵活转换攻略
优秀创作者
创作者俱乐部成员