遇到Excel噩梦?新同事的表让你崩溃?

遗忘美丽 2024-06-17 11:23:04

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

这是一个只有一列的员工信息表(如下图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

0 阅读:14

遗忘美丽

简介:自由自在