|
各位表亲好，今天我们一起聊聊从字符串中提取数字的技巧。实际工作中，难免遇上数字和其它内容混在一个单元格中的情况，而我们需要提取其中的数字内容。而根据数字和其它内容的性质，我们又分为以下几种情况分别处理。 一、连续的数字和汉字分离。 A1单元格内容“696477632欢迎加入初学者交流群”，我们注意到，部分内容为数字，其余全为汉字，且数字与汉字之间有明显的分界线，数字是连续的，汉字也是连续的，没有像”我1你2他3“这种间隔排列。提取数字的公式为=--LEFT(A1,2*LEN(A1)-LENB(A1))。LEFT函数:从字符串或者单元格引用的左侧提取指定位数的字符，比如=LEFT(“我爱中国”,2)表示从“我爱中国”这个字符串的左侧提取2个字符，结果为“我爱”。LEN和LENB函数都是返回参数的长度，不同的是，LEN函数不检测全角半角，有几个字符就返回几，LENB对于占两个字节的汉字和其它全角字符，返回2。比如len(“我”)的结果为1，而Lenb(“我”)的结果为2。具体到这个题目中，LEN(A1)结果为19，再乘2为38，而LENB(A1)结果为29，二者相减刚好为单字节内容的数量（数字的位数）。 Tips：1、LEFT为文本函数，返回的结果为文本格式，要得到数值，需要让它进行两次取负运算。 2、如果数字在右侧，把LEFT函数改为RIGHT即可。 3、如果是提取汉字，可以使用=RIGHT(A1,LENB(A1)-LEN(A1))。 4、使用公式=LOOKUP(10^10,--LEFT(A1,ROW($1:$10)))也可以得到预期结果。10^10表示10的10次方，这里假定数字不超过10位，文本，如果超过，需要将10^10和row($1:$10)适当扩大。 二、连续的数字在汉字中间 A1单元格内容“昨天花458元买了一支钢笔”。这样数字前后都有汉字，应该怎么提取数字内容呢？ 方法1：找到第1个数字所在位置，并使用上面提到的2*len(a1)-lenb(a1)的方法得到数字的位数，之后使用mid函数来提取。 先来查找第一个字符的位置公式为=MIN(FIND(ROW($1:$10)-1,A1&2/17))。 公式解析：row($1:$10)-1生成0-9的一个数组，使用find函数查找0-9各个数字在A1中的位置，然后使用min函数取最小值（即最先出现的位置）。但0-9的数字往往并不都现出在要查找的字符串中，比如这里就没有0，所以find函数会返回错误值#VALUE，导致min获取不到正确结果。所以我们可以在原字符串后连接一个由0-9组成的字符串，以保证find返回正确结果。而2/17(2除以17)的结果，恰好包含了0-9的所有数字。 得到了第一个数字出现的位置，现在我们就可以使用MID函数来提取：=MID(A1,MIN(FIND(ROW($1:$10)-1,A1&2/17)),2*LEN(A1)-LENB(A1))。这是一个数组公式，数组公式需要按Ctrl+Shift+Enter才能得到正确结果。 方法2：从第1位开始，依次取1-10位，构成二维数组（{'昨','昨天','昨天花','昨天花4','昨天花45','昨天花458','昨天花458元','昨天花458元买','昨天花458元买了','昨天花458元买了一';'天','天花','天花4','天花45','天花458','天花458元','天花458元买','天花458元买了','天花458元买了一','天花458元买了一支';'花','花4','花45','花458','花458元','花458元买','花458元买了','花458元买了一'…}），然后找出这个数组中最大的数。公式为=MAX(IFERROR(--MID(A1,ROW($1:$30),COLUMN($A:$J)),1=2))。这也是一个数组函数。 TIP:因为数组中多数为非数字内容做取负运算会得到错误值，所以加一层容错函数IFERROR，将错误值替换为FALSE（因为1=2的返回值为FALSE）。这样MAX函数就可以顺利取到最大值了。 提取方法不仅限于以上几种，篇幅限制，今天就举这几个例子。今天涉及到多个函数嵌套，需要大家慢慢理解并学习其中的思路，随着学习的深入，你会发现思路比掌握函数本身更加重要。 |
|
|
