大家知道Excel的强项是对纯数据进行高效处理，不过在日常工作中，总是由很多用户总是把Excel当Word用，在输入数据时总爱将数据和文本、货币符号等数据参杂混在一起。这样Excel无法发挥其数据处理作用，其实我们可以通过以下方法，在Excel中快速把夹杂各种符号的纯数据提取出来。
混杂“有规律，函数来提取
如果混杂的数据是比较有规律的，比如数字都是在文本的右边或者左边，那么借助内置函数LENB和LEN即可进行提取，假设原始数据如下图所示，数字都是在汉字的右侧。因为这里数字的数据都包含元，先点击“查找和选择→替换，在替换栏输入“元，替换为置空，点击“全部替换（图1）。
图1 替换不需要的字符
接着定位到C2输入公式“=RIGHT(B2,2*LEN(B2)-LENB(B2))，公式的意思是这里使用Right函数从B2右边开始截取字符，因为数字字符在右侧，通过函数就可以将右侧的数字全部截取出来。如果要统计数字，下拉公式后将其提取的数字粘贴（粘贴方式选择“数值123）到D列即可使用求和函数进行统计了。如果数字在左侧，则使用LEFT函数进行截取（图2）。
图2 统计杂乱数字
混杂无规律，组合函数来提取
上述的数字是有规律的分布在文字的左侧或者右侧，如果数字分布没有任何规律，比如下列的数据数字是分布在随机位置，那么上述函数就无能为力（图3）。
图3 混乱排列的数字
对于类似的数据提取需要借助组合函数进行提取，定位到C2输入公式“=-LOOKUP(,-MIDB(B2,SEARCHB("?",B2),ROW($1 8)))，然后下拉即可将原来混杂的数字全部提取出来（图4）。
图4 提取混乱数字
小提示：
嵌套函数从内到外依次解释：ROW($18)生成一个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）。
图5 字母数字汉字混杂数据
首先将数字列复制然后粘贴到Word中，点击“查找与替换，在查找内容输入 “^$ 全部替换为空，这样所有字母就会全部消除。这里^$是字母正则表达式通配符，表示查找文中所有的字母。这样完成替换后原来数据中的所有字母就全部消失了（图6）。
图6 替换字母
现在上述字符中剩下就全部是数字和汉字，同上继续打开查找与替换，在查找框输入“[一-龥] （查找全部汉字的通配符，“龥字是繁体，拼音为yù，谷歌输入法切换到繁体输入即可），点击更多勾选下方“使用通配符，这样就能查找所有中文字体，最后替换为空（图7）。
图7 替换汉字
这样通过上述方法替换后就只剩下所需的数字了，不过有些数字的还包含半角空格，再次使用查找替换工具（在查找内容中输入[^w]，替换为空）进行替换，最后将提取的数字全部复制回Excel中即可（图8）。
图8 替换汉字
当然Word的查找与替换功能要比Excel更为强大，对于类似需要在Excel中对字符提取或者替换，既可以直接将数据复制到Word中处理，也可以直接在Word中插入Excel表格，然后利用Word来高效处理。
|
发表于 2021-3-28 09:33:32
|
/1 
发表于 2020-11-1 06:41:28
