excel混合文本提取字符,excel中数字文字混合如何提取

  excel混合文本提取字符,excel中数字文字混合如何提取

  众所周知,Excel的强项是高效处理纯数据。但是在日常工作中,很多用户总是把Excel当Word用,在输入数据时总喜欢把数据和文本、货币符号等数据混在一起。这样,Excel就无法发挥其在数据处理中的作用。其实,我们可以通过以下方法快速提取Excel中混有各种符号的纯数据。

  

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

  如果混合数据是规则的,例如,数字在文本的右边或左边,那么它可以在内置函数LENB和LEN的帮助下提取。假设原始数据如下图所示,数字在汉字右侧。因为这里所有的数字数据都包含meta,所以先点击“查找选择替换”,在替换栏输入“meta”,用空白替换,点击“全部替换”(图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($1:$8)生成1-8的序列,即(1;2;3;4;5;6 .),即从1-8行原始数据开始搜索。SEARCHB(?),B2),在B2单元格中找到第一个半角字符,在哪里?表示任何单字符数据,SEARCHB(?)B2)指示从B2中搜索数字字符的位置,并将其用作MID截取的字符的位置。MIDB(B2,搜索b(?)、B2),表示在B2单元格中,从第一个半角字符开始截取的位数为1-8。前面加一个减号(-),截取的文本值就是数字,会转换成数字和负数。最后,通过Lookup查找序列中最后一个不大于0的值,并在前面加一个负号(-),这样就可以把原来的负值变成原来的值。这样,无论数字在字符中的位置如何,都可以从单元格中分离出所需的数字。

  

字母文字混杂,Word来协助

  以上所有函数都是利用数字(单字节)和汉字(双字节)的区别,通过查找单字节的字符来提取数字。如果原始数据包含单字节数据,比如字母,我们可以使用Word来查找和替换这样的数据(图5)。

  图5字母数字汉字的混合数据

  先将数值列复制粘贴到Word中,点击“查找替换”,在搜索内容中输入“$”替换所有空格,这样所有的字母都会被消除。这里$是字母正则表达式的通配符,意思是查找文本中的所有字母。这样,替换后原始数据中的所有字母都消失了(图6)。

  图6替换字母

  现在上面剩下的字都是数字和汉字。如上,继续搜索替换,在搜索框输入“[one-]”(查找所有汉字的通配符,字为繁体,拼音为Y,谷歌输入法可以切换到繁体输入)。点击更多勾选下面的“使用通配符”,这样就可以找到所有的中文字体,最后替换成

  图7替换汉字

  这样,通过上述方法替换后,只剩下需要的号码。但是,有些数字也包含半角空格。使用查找和替换工具(在搜索内容中输入[w]并用空白替换)再次替换它们,最后将提取的数字全部复制回Excel(图8)。

  图8替换汉字

  当然,Word的搜索和替换功能比Excel更强大。对于Excel中需要提取或替换的相似字符,可以直接将数据复制到Word中进行处理,也可以直接在Word中插入Excel表格,然后使用Word进行高效处理。

excel混合文本提取字符,excel中数字文字混合如何提取