本篇适合：公式基础较强者，主要思路分享。

公式小白请绕行→《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 总结

规范输入很重要！！！

规范输入很重要！！！

规范输入很重要！！！

重要事情说三遍。

作者：闲钓宇哥

原创不易，感谢关注。