Excel实战：提取号码
本篇适合：公式基础较强者，主要思路分享。
公式小白请绕行→《Excel-如何快速读懂别人的公式》《实例讲解：初学者如何组合函数》，公众号回复“读公式”、“公式组合”即可获取。
QQ交流群1：644328490（已满）
QQ交流群2：860692128（新开）
需求：如图-1提取号码
图-1丨提取号码
关键词：提取号码
01 手机号提取
原始数据有手机号，也有座机号，输入不规范，相当杂乱，想要一个公式解决，几乎不可能。这里从易到难，分好几步提取，最后验证并根据优先级，确认提取结果。
思路1：末尾提取手机号
凭肉眼观察，大多数手机号都在字符最右侧，联想到right函数。
验证条件有二：
①提取出的号码首位是1
②提取出的数字是11位
如图-2，B2处公式：
=IF(AND(--LEFT(RIGHT(A2,11),1)=1,--RIGHT(A2,11)>10^10),--RIGHT(A2,11),0)
图-2丨末尾提取手机号
思路2：常规手机号提取
借鉴人脑快速判断手机号思路：
①先找数字1；
②找到后查看，后10位都是数字？
③是数字，快速判断为手机号；不是数字，返回步骤①，找下一个1
可用数组公式，实现该思路。难点在于，如何让计算机知道，正在查找的1，是第几个；可嵌套substitute函数实现。详细思路解析，可阅读《第n次出现文本的位置》，公众号回复“n”即可获取。
C2处公式：
=MAX(IFERROR(--MID(提取号码!A2,FIND("闲钓宇哥",SUBSTITUTE(提取号码!A2,1,"闲钓宇哥",ROW($1:$20))),11),0))，ctrl+shift+enter三键结束输入。
图-2丨常规手机号提取
公式解析：
数组公式中，row（$1：$20）作为substitute的参数，意思是依次将第1至第20个“1”替换为“闲钓宇哥”。
外嵌find函数，意为查找第1至第20个“1”所在位置。
mid函数，提取11位字符。根据思路，文本中有n个“1”，会提取出n组11位字符（最多提取20组）。mid前面两个负号“--”，是将字符转换为数字，否则错误值。
外嵌iferror屏蔽错误值。
外嵌max，找到这些n组数字的最大值（注：实际不足n组）。可以粗略的认为：大部分最大值就是手机号。
D2处公式：
=IF(C2<10^10,0,C2)，向下填充，简单验证C列公式结果，剔除不足11位的数字。
图-3丨简单验证
思路3：数据预处理+常规手机号提取
D列筛0，可观察到，部分未筛选出手机号的原始数据中，存在“空格”与“-”，需要预处理删除，然后再提取一遍手机号。
为避免数据预处理的未知影响，故单独采用辅助列执行该思路。
E2处公式：
=TRIM(SUBSTITUTE(A2,"-",""))，向下填充。
图-4丨数据预处理
F、G列公式再执行一遍思路2，公式略。
确立图-5优先级，逻辑推理，略。
图-5丨优先级
据优先级，H2处公式：
=IF(ISNUMBER(B2),B2,IF(D2>0,D2,G2))，向下填充。意为综合三种思路，选出“最为合理”的那个手机号。
02 座机号提取
思路1：提取类似0*-*的座机号
星号*为通配符，例如0731-8277155、021-5648322 等，都是类0*-*座机号。
为方便查找，通过观察，我们将类0*-*座机号，划分为：
①类0??-???????座机号
②类0??-??????？座机号
③类0???-???????座机号
④类0???-????????座机号
注：其他类型，要么十分少见，要么本身错误，故不考虑公式，强迫者可人工处理。
图-6丨座机号归类
问号?为：一个字符的通配符。如果有一个数字的通配符，更好，然并无。
数据预处理，删除空格，I2处公式：
=TRIM(A2)，向下填充。
图-7丨数据预处理
如查找类0??-???????座机号，J2处公式：
=MID(I2,SEARCH("0??-???????",I2),11)，向下填充。
图-8丨座机号提取
另3类同理，添加辅助K、L、M列设置公式。
然后将第一个“0”与第一个“-”，替换为空，验证是否为数字。如N2处公式：
=IFERROR(--SUBSTITUTE(SUBSTITUTE(J2,0,"",1),"-","",1),0)，向下填充。
图-9丨验证提取的座机号
同理验证K、L、M列：N2公式向右拉3列，并向下填充。
可通过取四个验证的最大值，得到最终结果。故R2处公式：
=INDEX(J2:M2,MATCH(MAX(N2:Q2),N2:Q2))，向下填充。
index+match组合函数的详解，可阅读《分享Excel：高级查找篇之一（多）对多查找》《加强版查找公式》，公众号回复“一对多”、“查找”即可获取。
图-10丨座机号提取结果
思路2：提取纯数字座机号
因输入不规范，少数座机号没有“-”，导致类0*-*座机号提取失败。这时还可借鉴手机号提取思路：
①先找数字0；
②找到后查看，后12位都是数字？
③是数字，快速判断为座机号；不是数字，返回步骤①，找下一个0
S2处公式：
=MAX(IFERROR(--MID(A2,FIND("闲钓宇哥",SUBSTITUTE(A2,0,"闲钓宇哥",ROW($1:$21))),12),0))，三键结束输入，向下填充。
公式解析，略。
图-11丨纯数字座机号提取
T2处公式：
=IF(S2>10^9,S2,0)，向下填充。
简单验证S列结果是否座机号。
U2处公式：
=IF(T2>0,"0"&T2,0)，向下填充。
将0添加回去，还原座机号。
因本思路结果，验证不甚严谨，故优先级处于下一层。
综合两种思路，确认座机号提取结果，V2处公式：
=IFERROR(IF(ISERROR(R2),U2,R2),0)，向下填充。
图-12丨座机号提取结果
03 总结
规范输入很重要！！！
规范输入很重要！！！
规范输入很重要！！！
重要事情说三遍。
作者：闲钓宇哥
原创不易，感谢关注。
