|
大家好，我经常看到有些同学会问一些从字符串中提取多个数字的问题，可能是ta数据录入不规范的原因，也可能是从系统导出的数据。不管什么原因，我认为这个问题也挺常见的，所以就学习和研究了好几天，今天就来和大家分享下提取的方法。 首先看下源数据和提取后的效果，见下图。A列是源数据，记录了一些信息，每条信息包含多个数字，有正数，负数，小数。现在要将这些数字分别提取出来，而BCD列就是提取后的效果。 由于数据不规范，用函数提取就比较费事，所以公式会很长，不过不用担心，只要理清思路，公式就是水到渠成。所以先不写公式，先来说下思路。 以第1条数据为例，首先要找到每个数字最后一位所在的位置，如下红色箭头所示，例如6488中最后一个8的位置为7。找到位置后就用left从左边提取出来，就变成"陈全俊6488"，然后再用right从右边提取4位，就得到了6488。当然这个4位是用其他函数算出来的。其他2个数字也是这样提取出来的。这就是一个大概的思路。 第一步，怎么找每个数字最后一位的位置？观察一下，我们可以发现最后一位的数字都有一个特征，它自己是数字，同时它后面的一位是文本。比如6488中的最后一个8是数字，8后面的逗号是文本。其他的最后一个数字都有这个规律。 所以我们将这个字符串按一个字符一个字符的提取出来，{"陈";"全";"俊";"6";"4";"8";"8";"，";"手";"机";"号";"1";"5";"6";"4";"5";"1";"5";"5";"2";"3";"8";"，";"北";"京";"-";"1";"0";".";"0";"2";"℃"}，然后判断它自己是数字，并且它的下一位是文本，如果这2个条件同时满足，就返回它的序号，也就是它的位置。 实际它们有一个错位的关系，公式为ISNUMBER(-MID($A2,ROW($1:$99),1))*ISERR(-MID($A2,ROW($2:$100),1))。这就是我说的2个条件，第1个的isnumber是判断它是否是数字，第2个iserr是判断它的下一位是否为文本。其实还有第3个条件，是小数点的问题。比如下图中的-10.02中的0是数字，它的后一位是小数点，也是文本，符合我们上面说的条件，但我们不会在这个位置提取。所以它的下一位是文本但不能是小数点。(也就是将小数点排除出去）。 3个条件是这样的：ISNUMBER(-MID($A2,ROW($1:$99),1))*ISERR(-MID($A2,ROW($2:$100),1))*(MID($A2,ROW($2:$100),1)<>".")，第1个是判断它是数字，第2个判断它的下一位是文本，第3个判断它的下一位不等于小数点。 这3个条件同时满足，返回它的位置。IF(ISNUMBER(-MID($A2,ROW($1:$99),1))*ISERR(-MID($A2,ROW($2:$100),1))*(MID($A2,ROW($2:$100),1)<>"."),ROW($1:$99))。这样就找到每个数字最后一位的位置了。它们在一个数组中，所以用small分别提取第1个位置，右拉提取第2个位置，再右拉提取第3个位置。有点像万金油公式的套路，满足条件的找到后，返回它的行号，然后用small分别提取每个行号。 比如我们用small提取出第1个位置7后，用left从左边提取7位，得到"陈全俊6488"。然后用right从右边提取，提取几位不确定，那我们就用数组提取，然后配合lookup找到6488。如下图所示，具体用法就不详细展开了。这样就得到了我们想要的数字。 完整的公式为=IFNA(LOOKUP(9E+307,--RIGHT(LEFT($A2,SMALL(IF(ISNUMBER(-MID($A2,ROW($1:$99),1))*ISERR(-MID($A2,ROW($2:$100),1))*(MID($A2,ROW($2:$100),1)<>"."),ROW($1:$99)),COLUMN(A1))),ROW($1:$99))),"")，按ctrl+shift+enter三键结束，向右向下填充。 要注意的地方是这个公式对有些数字可能提取不准确，比如前面带0的，像0352；还有一些文本型的日期也可能提取不准确。 公式比较长，又用了很多数组，用文字版不好讲解，大家如果感兴趣的话，可以按我开始说的思路去理解，同时下载文件边按F9边查看公式。 链接： https://pan.baidu.com/s/1mkx48h8MTgejsZBCmcj7iQ 提取码：wafy |
|
|
