文本数字拆分技巧

Excel处理人员呢,最喜欢的就是规范化的表,那什么样子的表是规范的呢?给大家个图片感受一下!

今天的要和大家分享的就是和规范化图表格格不入的,需要由不规范变成规范的!

再给大家一个图片感受一下!

这是谁做的表!!!

原始表的销售数量和销售人员的姓名是混合在一起的,让我怎么弄!!!

我们需要把他拆分成两个不同的单元格,怎么处理呢?

方法一:智能快速填充法

神马是智能快速填充大法呢!我感觉用文字和截图已经无法表达我内心的激动和喜悦啦,只能直接上GIF图!

有些人说我的电脑没有这个应用哦!

那不好意思,请做普通人,说普通话,使用13及以上版本的office!

简单说一下什么是快速填充哈!

快速填充就是智能填充,他根据已填写的内容和同行单元格的内容判断你的逻辑关系,比如都是截取的是数字,那后续填充的就都是数字!姓名同理!

提醒:由于是Excel帮我们判断,所以难免他会判断错误!所以本方法不是万能的!能解决93.176%的问题吧!

不要问我百分比是怎么来的!我会认为你很可爱!

方法二:利用文本数字字符宽度不同截取

之前我上课的时候分享文本函数Left、Right、Mid的时候经常说的一句话是:“在大多数情况下,无论数字和汉字,在Excel中都是一个字符长度。”

那什么时候是少数情况呢?

现在就是少数情况!

先介绍一下LEN函数,他是返回某个单元格的字符长度!无论中英文还是符号每个都是1个长度!

So 368毕春艳用Len函数返回值是6!

LENB函数呢!他会将汉字计算为2个长度的字符、数字符号还是1个长度的字符!

所以368毕春艳用LenB函数返回值是9!

然后发现什么规律了没有!用LenB函数获取的值减去Len函数获取的值等于虾米呢!

LenB函数-Len函数的返回值,等于实际汉字的个数

2*Len函数-LenB函数的返回值,等于实际数字的个数

考验你数学计算能力的时候到了!!!!

最后结果是虾米呢!

获取数字:

公式:=LEFT(E2,2*LEN(E2)-LENB(E2))

Left函数不解释咯~~

获取名字:

公式:=RIGHT(E2,LENB(E2)-LEN(E2))

方法三:Lookup巧应用

前两个方法都是很简单实用的!

接下来单纯为了分享Lookup的用法而分享的!

Lookup三个参数向量形式的我之前的很多文章都有分享,今天分享两个参数数组形式的!

=Lookup(找什么,在哪里好) 返回小于第一个参数的,在第二个参数的最后一个值!

备注:我没说第二个参数要按照升序排序哦!

本案例如何实现呢!

公式:{=-LOOKUP(1,-LEFT(E2,ROW($E$1:$E$19)))}

本次又到了我喜欢说的一句话的时候“数组大法好!!!”

不知道各位朋友发现没有,小编对数组有特殊偏好的!

咳咳,回归正传!

公式:{=-LOOKUP(1,-LEFT(E2,ROW($E$1:$E$19)))}

先说明一下{ } ,这个大括号不是手动输入的,而是数组函数应用的时候,同时按住Ctrl+Shift+Enter自动生成的!

本公式不太好理解的几个点:

1、Left函数里面的ROW($E$1:$E$19)干嘛用的!

2、为什么第二个参数和公式前面加上了负号

3、为什么第一个参数是1

综述:这个函数到底是啥意思!!!!

施主莫急,待老衲慢慢道来!

Left()函数比较好理解,截取某个文本左面的N个字符!

Row()函数是用来获取单元格的行数的

1、ROW($E$1:$E$19)返回值是虾米呢!

{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19},一个1到19的数组

2、LEFT(E2,ROW($E$1:$E$19))返回值是虾米呢!

{"3";"36";"368";"368毕";"368毕春";"368毕春艳";"368毕春艳";"368毕春艳";"368毕春艳";"368毕春艳";"368毕春艳";"368毕春艳";"368毕春艳";"368毕春艳";"368毕春艳";"368毕春艳";"368毕春艳";"368毕春艳";"368毕春艳"}

前三个是数字,后面的都是数字和文本的混合体

3、-LEFT(E2,ROW($E$1:$E$19))返回值是虾米呢!

{-3;-36;-368;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}

为什么后面变成报错了呢!

因为只有数字负数有意义!文本是没有负数的!

知道为啥要加个负号了吧!

4、Lookup第一个参数是1,第二个参数是上面那一串,返回值是什么啦?

返回第二个参数中小于第一个参数且最后面的值!

那返回是虾米呢!-368

5、整个函数的返回值是虾米呢!

368!

到目前为止,刚刚提出的四个疑问解决了没?

多说几句!

1、Row函数里面的参数个数可以是任意长度,但是要大于最大的数字长度

2、第一个参数可以是1,也可以是任意正数

留两个思考题!

1、如果数字在右面怎么获取?

2、如何获取姓名呢?

之前想分享文本数字混杂(23香蕉61苹果)在一起怎么拆分的!文章太长,下期再分享!

感谢收看!下期不见不散!

原文发布于微信公众号 - 阿凯的Excel(akexcel)

原文发表时间:2017-09-09

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数值分析与有限元编程

可视化 | 一个三角形常应变单元后处理例子

昨天提到了应力云图,其实质是用不同的颜色填充等值线。有了结点的应力值,单元内任意一点的应力值是通过插值实现的。下面来看一个悬臂梁的综合后处理。 如图所示,一个悬...

2537
来自专栏生信宝典

为啥我的Python这么慢 - 项查找 (二)

上一篇为啥我的Python这么慢, 字符串的加和和join被陈群主分享到biopython-生信QQ群时,乐平指出字典的写法存在问题,并给了一篇知乎的链接htt...

1729
来自专栏欧阳大哥的轮子

常用的数学函数以及浮点数处理函数

在编程中我们总要进行一些数学运算以及数字处理,尤其是浮点数的运算和处理,这篇文章主要介绍C语言下的数学库。而其他语言中的数学库函数的定义以及最终实现也是通过对C...

662
来自专栏专知

Numpy教程第1部分 - 阵列简介(常用基础操作总结)

【导读】这里是numpy教程的基础部分,涵盖了使用numpy的ndarrays执行数据操作和分析的一些操作。众所周知,Numpy是Python中最基本和最强大的...

2604
来自专栏take time, save time

你所能用到的数据结构(九)

十二、为了count的最终胜利 在介绍完最基本的堆栈模型之后,下面要继续的是第二种最基本的模型,队列。队列,在现实生活中经常可以看到(不过考虑到在我国大部分人...

2647
来自专栏阿凯的Excel

巧妙完成二维表的数据匹配

接下来我将每周分享一个广大网友向我提问的经典问题。 本周问题,如何对二维表进行匹配! 原表格! ? 备注:以上人名,均属虚构,如有雷同!说明有缘!!! 咳...

2673
来自专栏工科狗和生物喵

【计算机本科补全计划】指令:计算机的语言(MIPS) Part3

正文之前 今天学的很尴尬,因为有事情,而且新认识了两个计算机学院的保研大佬,不得不感叹我找的导师之强,第一个去上交的,是被金老师推荐去的,听说是跟了目前亚洲第一...

2908
来自专栏挖掘大数据

处理海量数据的10种常见方法

本文将介绍10种处理海量数据问题的常见方法,也可以说是对海量数据的处理方法进行一个简单的总结,希望对你有帮助。

20210
来自专栏对角另一面

读lodash源码之从slice看稀疏数组与密集数组

卑鄙是卑鄙者的通行证,高尚是高尚者的墓志铭。 ——北岛《回答》 看北岛就是从这两句诗开始的,高尚者已死,只剩卑鄙者在世间横行。 本文为读 lodash ...

1900
来自专栏菜鸟前端工程师

JavaScript学习笔记002-数据类型0字符串拼接

[十位] [个位] [几何] [子集] [大圆] [小圆] [元素] [下标] [分子] [分母] [分数] [中点] [约分] [加数] [减数]

751

扫描关注云+社区