[套路合集]-函数法字符提取与重构18式

流浪铁匠

题外话,我是个喜欢发原创内容的,不怎么喜欢发这种纯整理性质的帖子（因为套路都被前辈们挖得差不多了没什么自己的新东西）


字符的提取问题,是excel函数的一类常见问题,很多招式都有独到之处                                                                        
这篇收集了在江湖上的一些常见招式,即便练不成降龙十八掌,能练成降龙十巴掌也足以混迹江湖了                                                                        
                                                                        
最基础的字符提取,是利用文本函数进行最简单的规律提取                                                                        

                                                                        
本文主要就是围绕各类字符串的提取/重组等介绍一些常见(与不常见)的套路                                                                        
希望能对大家有所助益                                                                        

流浪铁匠

第1章  基础篇



这2个函数一般可以理解为len计字符数,lenb计字节数                                
利用2个结果的差值,可以获取字符串内单字节字符/双字节字符的总数                                
(一般来说汉字为双字节字符,数字/英文/常用符号为单字节字符)                                
注意此招罩门在于默认语言,仅适合中文等,不适合英文



第2章 定位篇
本章主要介绍定位各种性质字符的套路

流浪铁匠

第三章 套嵌应用篇
函数套嵌组合下实现的各种提取效果

jivy1212

=FILTERXML(SUBSTITUTE("<a><b>"&A18&"</b></a>","-","</b><b>"),"//b[last()]")这个很好玩。。有没有老师可以扩展下这个参数的技巧，我就只知道"a/a[.<99]"这个技巧（分列后只取符合条件的数值）。论坛出的书上也没有讲这个的。

流浪铁匠

第四章  数组运算篇

流浪铁匠

前面的只是开胃菜,本篇才是正题

第五章 函数进阶篇











利用对相邻字符的判断来定位位置进行提取的可怕思路


非汉字

1. =MID(LEFT(A130,SMALL(IF(MMULT(N(MID(A130,ROW($1:$99)+{0,1},1)<"吖"),{2;1})=2,ROW($1:$99),99),F130)),SMALL(IF(MMULT(N(IFERROR((MID(A130,ROW($1:$99)+{-1,0},1)<"吖"),)),{2;1})=1,ROW($1:$99),99),F130),99)

复制代码

汉字

1. =MID(LEFT(A130,SMALL(IF(MMULT(N(MID(A130,ROW($1:$99)+{0,1},1)>="吖"),{2;1})=2,ROW($1:$99),99),F130)),SMALL(IF(MMULT(N(IFERROR((MID(A130,ROW($1:$99)+{-1,0},1)>="吖"),)),{2;1})=1,ROW($1:$99),99),F130),99)

复制代码

英文

1. =MID(LEFT(A130,SMALL(IF(MMULT(N(ISNUMBER(AREAS(INDIRECT(MID(A130,ROW($1:$99)+{0,1},1)&1)))),{2;1})=2,ROW($1:$99),99),F130)),SMALL(IF(MMULT(N(IFERROR(ISNUMBER(AREAS(INDIRECT(MID(A130,ROW($1:$99)+{-1,0},1)&1))),)),{2;1})=1,ROW($1:$99),99),F130),99)

复制代码

(附件调整时行数变化了,显示的公式没调整，单元格位置自己改下,这篇主要是为了列套路的)


18,未完待续                                       
星爷的"武状元苏乞儿"中,降龙十八掌是只有前17掌的,把这17掌合成一掌就是第18掌,(在此致敬下经典)                                       
这里也是                                       
提取字符的方法和思路太多,永远没有终点                                       
见招拆招,把本文所列的各种思路融会贯通,希望你能自己练成自己的第18掌,谢谢                                       
                                       
以上,均为纯函数手段的字符提取                                       
当然,实际工作中,迭代运算,word的替换/vba配合正则表达式/power query的m函数等等                                       
都能实现一些函数很难完成的字符提取问题                                       
本文仅仅列举了一些纯函数思路的解决方式,只为给新手们开拓一些思路,谢谢                               
                                       

流浪铁匠

jivy1212 发表于 2019-6-13 12:43
=FILTERXML(SUBSTITUTE(""&A18&"","-",""),"//b[last()]")这个很好玩。。有没有老师可以扩展下这个参数的技 ...

只会一些简单的，譬如按照固定分隔符分列……



这个函数据说需要掌握html相关知识才能灵活应用

1055751654

铁匠老师，找数字的还有个特好记=MATCH(1,1^MID(A1,ROW(1:99),1),)有逗号是第一个，无逗号是最后一个

jivy1212

流浪铁匠 发表于 2019-6-13 13:01
只会一些简单的，譬如按照固定分隔符分列……

额，就是缺少高深技巧的，总感觉会有很多，会比老套路舒服。

流浪铁匠

1055751654 发表于 2019-6-13 13:55
铁匠老师，找数字的还有个特好记=MATCH(1,1^MID(A1,ROW(1:99),1),)有逗号是第一个，无逗号是最后一个

找第1个没问题
如果缺省match 3参找最后1个
此时 a1 如果是=1&REPT("a",48)&1&REPT("a",48)&1 时结果就错误了
你的公式和 7-4 / 7-7的思路类似

当然字符提取的公式没有万能的,这篇文章的主要目的之一就是展现一个提取问题有很多种方法
总要根据数据源决定最适合的那种罢了