首页 › EXCEL教程 › excel提取不规则文本中的数字，史上最全的excel提取数字方法来了

excel提取不规则文本中的数字，史上最全的excel提取数字方法来了

小智教你学办公

2024-08-14 18:22:27

先来看一下以下5种方法提取数字的效果

1、当数字在最左边时

excel提取不规则文本中的数字，史上最全的excel提取数字方法来了-趣帮office教程网

红色部分表示计算错误

解释含义：

方法1：直接用Left取左边两位文本，当单元格内数据有且只有2位的时候可以用此方法，便捷快速高效。或者直接用control E快捷键搞定。

方法2、利用字符和字节的长度差异可以求出剩余字节的长度，也就是数字和字母的长度。汉字位2个字符串，数字和字母为1个，此方法有弊端，如上图方法2红色部分。此方法可用于单元格内只有汉字和数字的情况。

方法3、用lookup来查找数字，SEQUENCE(4)结果为：{1;2;3;4}，为什么是4？按照单元格内数字的最大位数取，否则数据不全。当然也可以是5或者6或者100都可以。LEFT(L19,SEQUENCE(4))={"1";"12","123","1234"}，left函数直接让他从左边开始分别取1位文本，2位文本，3位文本，4位文本。当然了，这样取出来的数字还是文本格式，无法直接进行计算，可以在前面加一个“-”，或者把-LEFT(L19,SEQUENCE(4))改成LEFT(L19,SEQUENCE(4))*1都是可以的。结果都是把文本转换成数字，目的就是要让他跟前面lookup后面的1作比较。-LEFT(L19,SEQUENCE(4))={-1;-12;-123;-1234}。数字没有双引号就说明非文本格式了，可以直接参与运算。-LOOKUP(1,-LEFT(L19,SEQUENCE(4)))=-LOOKUP(1,{-1;-12;-123;-1234}),意思就是比-1小且最后一次出现的数字，即-1234，lookup前面有一个“-”号，负负为正。当然不一定非得是1，可以是取一个很大的数跟他作比较，8^6,6^6都是可以的，后面left前面的“-”就不能要了。

方法4、TEXTSPLIT这个函数是拆分单元格用的。比如：123-234-456，用textsplit(123-234-456,"-")，结果就是123 234 456数组，再复制粘贴成数字就分成三个单元格了。你也可以理解为此函数的目的是去除某些特定的条件再拆分。跟textjoin是相反的，textjoin是合并，以什么样的方式合并。回到本文，TEXTSPLIT(L19,{0,1,2,3,4,5,6,7,8,9,","},,1)={"abc"},意识就是把单元格内含有0~9和“,”的地方去掉再分割开来，函数的第三个和第四个条件可以忽略，1=true是忽略空单元格的意思。TEXTSPLIT(L19,TEXTSPLIT(L19,{0,1,2,3,4,5,6,7,8,9,","},,1),,1)={“12”}，意思就是再次把abc从原始单元格（12abc）剔除掉，就只剩12了，当然此时的12还是文本格式，按照实际需求需要继续运算的话前面加“--”或者*1。此函数的逻辑思想是拆分再拆分并去掉非数字的部分。此函数可以提取汉字、数字、字母

方法5、此函数是个万能函数。可以查找多个数字并组合到一起。CONCAT(IFERROR(--MID(L19,SEQUENCE(20),1),""))，适用于任何条件。MID(L19,SEQUENCE(20),1)={"1","2","a","b","c","","","","","","","","","","","","","","",""},意思就是分别从第1位，第2位，第3位...第20位开始取，只取1位，跟拆分是一个道理，比拆分更简单易懂。--MID(L19,SEQUENCE(20),1)，前面加“--”转换为数字格式，否则就是错误，再用iferror排除掉错误的部分，IFERROR(--MID(L19,SEQUENCE(20),1),"")={"1","2","","","","","","","","","","","","","","","","","",""}，再用concat连在一起，连在一起后还是文本格式，需要继续运算加“--”或者*1。此函数的逻辑是将所有文本逐个拆分开来排除非数字部分再来合并。跟textsplit有异曲同工之妙。SEQUENCE(20)的中的20代表单元格内文本的长度，字符的长度（有多少个）。此函数还可以按照制定条件提取数字，比如>100,<>10等等

方法6、此函数也是万能函数，文本可以杂乱无章，但是数字只能有一组，如果有多组数据只显示第一组数据。SEQUENCE(4)同理，4代表单元格内数字的最大位数。L19&1/17=“12abc0.0588235294117647”，为什么是1/17？因为1/17结果包含了0~9的所有数字。FIND(SEQUENCE(4)-1,L19&1/17)=“6,1,2,13”，是查找SEQUENCE(4)-1={0,1,2,3}在L19&1/17=“12abc0.0588235294117647”中出现位置，分别是第6个位置，第1个位置，第2个位置，第12个位置。再用min数字选取最小的，意思就是从第一个出现数字的位置开始取，这就是这个函数为什么只能去最前面的数据。此函数有点烧脑，感兴趣的同学可以私聊我。

2、当数字在中间时

excel提取不规则文本中的数字，史上最全的excel提取数字方法来了-趣帮office教程网