当前位置: 欣欣网 > 办公

新同事发来一个Excel客户信息表,气的想辞职!结果写了一个万能提取公式!

2024-06-17办公

小明的新同事昨天发来的一个客服信息表,气的小明想辞职!

这是一个只有一列的员工信息表(如下图A列),内容极其混乱。要求提取出收货人、手机号和地址(如下图BCD列)

  • 有的没联系人

  • 有的号码前没有手机,有的是手机,有的是手机号

  • 有的分隔符是分号,有的是逗号

  • 收货人、手机号和地址的前后顺序也不一致。

  • 可以有不少同学想试试Ctrl + E,兰色就替你们试一下最简单的手机号:

    放弃幻想,小明只能想法写公式,结果还真写出来一个万能提取公式。

    =LET(X,TEXTSPLIT(IF(COUNTIF(A3,"*收货人*")=0,"收货人: "&A3,A3),{",","收货人","地址",":","手机","手机号"},,1),SORTBY(X,LEN(X),1))

    是不是被长长的公式吓到了?嘿嘿,别怕,等兰色把整个公式思路解析后就会明白原理,相们对于新手来说肯定会有很多收获的。

    思路解析:

    自从textsplit函数问世,拆分字符变得无比简单。本例也不例外,也要用到它。但问题是有的信息少了收获人,所以需要补齐(如果缺其他项,同样方法)

    =IF(COUNTIF(A3,"*收货人*")=0,"收货人: "&A3,A3)

    注:收获人后 个空格(一般人名最多4个字)

    接下来就可以用textsplit函数拆分了

    =TEXTSPLIT(IF(COUNTIF(A3,"*收货人*")=0,"收货人: "&A3,A3), {",","收货人","地址",":","手机","手机号"} ,,1)

    注:大号括{ }内是所有可能的拆分符。

    新问题来了,拆分出来的是乱序的。这时该sortby出场了,利用收货人、手机号和地址的字符串长度进行排序。最终公式为:

    =LET( X ,TEXTSPLIT(IF(COUNTIF(A3,"*收货人*")=0,"收货人: "&A3,A3),{",","收货人","地址",":","手机","手机号"},,1), SORTBY( X ,LEN( X ),1) )

    注:因为sortby要两次使用拆分后的内容,为了简化公式,用定义名称(x)的函数 LET

    兰色说 :虽然兰色进行了说明,估计不是有很多同学看不太懂公式的含义,原因是大家对新函数没见过或不熟悉。上周六介绍了 let、Lambda 等4个新函数用法( 教程也同步更新到了兰色的函数大全大教程中 ),本周日上午兰色将大家继续学习新函数的用法,想学的点下面预约

    如果你没时间看直播或想看视频讲解,可以购买兰色的四合一大全教程。 包括 Excel表格92个函数用法(更新至148集) 119个使用技巧 透视表从入门到精通50集 图表从入门到精通185集 ,。详情点击下方链接: