再杂也无惧 在Excel高效提取数字

CFan 电脑爱好者 2019-01-15 09:03应用 标签:技巧

大家知道Excel的强项是对纯数据进行高效处理,不过在日常工作中,总是由很多用户总是把Excel当Word用,在输入数据时总爱将数据和文本、货币符号等数据参杂混在一起。这样Excel无法发挥其数据处理作用,其实我们可以通过以下方法,在Excel中快速把夹杂各种符号的纯数据提取出来。

混杂“有规律”,函数来提取

如果混杂的数据是比较有规律的,比如数字都是在文本的右边或者左边,那么借助内置函数LENB和LEN即可进行提取,假设原始数据如下图所示,数字都是在汉字的右侧。因为这里数字的数据都包含元,先点击“查找和选择→替换”,在替换栏输入“元”,替换为置空,点击“全部替换”(图1)。

201724tqsz1

图1 替换不需要的字符

接着定位到C2输入公式“=RIGHT(B2,2*LEN(B2)-LENB(B2)),公式的意思是这里使用Right函数从B2右边开始截取字符,因为数字字符在右侧,通过函数就可以将右侧的数字全部截取出来。如果要统计数字,下拉公式后将其提取的数字粘贴(粘贴方式选择“数值123”)到D列即可使用求和函数进行统计了。如果数字在左侧,则使用LEFT函数进行截取(图2)。

201724tqsz2

图2 统计杂乱数字

混杂无规律,组合函数来提取

上述的数字是有规律的分布在文字的左侧或者右侧,如果数字分布没有任何规律,比如下列的数据数字是分布在随机位置,那么上述函数就无能为力(图3)。

201724tqsz3

图3 混乱排列的数字

对于类似的数据提取需要借助组合函数进行提取,定位到C2输入公式“=-LOOKUP(,-MIDB(B2,SEARCHB("?",B2),ROW($1:$8)))”,然后下拉即可将原来混杂的数字全部提取出来(图4)。

201724tqsz4

图4 提取混乱数字

小提示:

嵌套函数从内到外依次解释:ROW($1:$8)生成一个1-8的序列,即(1;2;3;4;5;6......),也就是从1-8行原数据里进行查找。SEARCHB("?",B2),查找B2单元格中第一个半角字符,其中?代表任何单字符数据,SEARCHB("?",B2) 表示从B2开始查找一个数字字符所在的位置,并将其作为MID截取字符的位置。MIDB(B2,SEARCHB("?",B2),,表示在B2单元格中,从第一个半角字符开始截取,截取位数为1-8位的数字。前边再加负号(-),将截取的文本数值中是数字的转变为数值且变为了负数。最后通过Lookup查找序列中不大于0的最末尾的值,前边再加负号(-),将原来变成负数的值再变为原值。这样即可从单元格中分离出的所需的数字,而不需要理会数字在字符中的什么位置。

字母文字混杂,Word来协助

上述函数都是利用数字(单字节)和汉字(双字节)不同,通过查找单字节的字符来实现数字的提取。如果原始数据包含字母等单字节的数据,这类数据我们可以借助Word的查找与替换来实现提取(图5)。

201724tqsz5

图5 字母数字汉字混杂数据

首先将数字列复制然后粘贴到Word中,点击“查找与替换,在查找内容输入 “^$ ”全部替换为空,这样所有字母就会全部消除。这里^$是字母正则表达式通配符,表示查找文中所有的字母。这样完成替换后原来数据中的所有字母就全部消失了(图6)。

201724tqsz6

图6 替换字母

现在上述字符中剩下就全部是数字和汉字,同上继续打开查找与替换,在查找框输入“[一-龥]” (查找全部汉字的通配符,“龥”字是繁体,拼音为yù,谷歌输入法切换到繁体输入即可),点击更多勾选下方“使用通配符”,这样就能查找所有中文字体,最后替换为空(图7)。

201724tqsz7

图7 替换汉字

这样通过上述方法替换后就只剩下所需的数字了,不过有些数字的还包含半角空格,再次使用查找替换工具(在查找内容中输入[^w],替换为空)进行替换,最后将提取的数字全部复制回Excel中即可(图8)。

201724tqsz8

图8 替换汉字

当然Word的查找与替换功能要比Excel更为强大,对于类似需要在Excel中对字符提取或者替换,既可以直接将数据复制到Word中处理,也可以直接在Word中插入Excel表格,然后利用Word来高效处理。