首页 函数技巧正文

拒绝加班,如何把excel单元格中海量的不规则的文字和数字拆分出来

前些天,一个小伙子新同事愁眉苦脸地跟我诉苦,说其他部门同事完全没有一点excel操作能力,交给他的一些表格太令人头疼了,全是杂乱无章的,其中一个研发人员的表格被名单挤爆了,而他作为后续跟进人员,不得不要把这些表格拆分出来,以便后续工作能顺利进行,而他也是一名新手,而对海量数据,也没有太多办法;

默默心疼他一秒钟,后面帮他看了一下,提供了一些方法,这里截取一部份放上来,其实处理起来也不算是太难,关键是思路,下面先上图:

公式:trim(mid(substitute($J$1833,"、",rept(" ",1000)),row(A1)*1000-999,1000))

现在来讲解一下公式思路,先用SUBSTITUTE函数把C3中的"、"号替换掉,再用MID函数将各个名字截取出来,至于TRIM函数则是把单元内文字或是数据前后的空格去掉,因为这表格之前完全是由手工录入,可能会有些不规则的;

但是这样做还是不能够达成要求,所以还要再来点其他思路,把"、"换成空格,然后把空格放大,再来截取数值,这就成了,

1、SUBSTITUTE($J$1833,"、",rept(" ",1000)),是把单元格里的"、"换成空格,而且把这个空格重复1000个(这个按需吧),为什么要这么多呢,看第2点;

2、ROW(A1)*1000-999,通过函数就知道,这个结果值是为1,1001,2001,3001...,这个函数的位置是在MID函数的截取开始的位置,也就是说要强制从这些准确的位置开始来取数,但是空格加上原来的汉字,强制这些长度有用吗?,看第3点;

3、MID(SUBSTITUTE($G$3,"、",REPT(" ",1000)),ROW(A2)*1000-999,1000),就是mid(字符串,开始位置,字符个数),计算出第一个结果肯定是没问题,那么要取第二个名字时,它的公式值应该是mid($G$3,1001,1000),1001和1000的位置都是空格,出来的结果不就是,/   汉字   /,值的前面和后面都有空格,这能合要求吗?这下就需要用到TRIM函数了;

4、通过TRIM函数,把mid($G$3,1001,1000)出来的结果值前后的空格都去掉啦,所以是OK的啦!


相信这些函数大家都会,思路通了,就很快能得到结果了,工作效率也非常快,当别人在加班时,你就可以很愉快出去浪了!

打赏