如何才能用公式把固定电话的区号全部给去掉

卢很肥

沉默式、Kiss 发表于 2016-5-28 21:32
首先，感谢各位大神的帮助，我的思路是这样的，先用分列功能全部区别手机跟固话，然后判断1开头的肯 ...

看你楼上，区号位数规律

kane027

1. =IFERROR(SUBSTITUTE(MID(TRIM(MID(SUBSTITUTE(" "&A2,"/",REPT(" ",50)),FIND(" 0",SUBSTITUTE(" "&A2,"/",REPT(" ",50))),50)),5-COUNT(FIND("/0"&{1,2},"/"&A2)),19),"-",),"")

复制代码

固定电话提取修正，
1、增加识别010开头的固话
2、规避掉区号后面出现的 “ - ”

horselyq

三位区号的城市只有10个北京：010,广州：020,上海：021,天津：022,重庆：023,沈阳：024,南京：025,武汉：027,成都：028,西安：029.
只有01和02开头的是三位，其他开头的都是四位

固话的提取公式(含区号），右拉可以依次得到第1，2，3，……个号码

1. =TRIM(LEFT(SUBSTITUTE(MID(SUBSTITUTE("/"&$A2,"/0",REPT(" ",99)&0,COLUMN(A1)),99,99),"/",REPT(" ",99)),99))

复制代码

固话的提取公式(不含区号），右拉可以依次得到第1，2，3，……个号码

1. =MID(TRIM(LEFT(SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("/"&$A2,"/01","/001"),"/02","/002"),"/0",REPT(" ",99)&0,COLUMN(A1)),99,99),"/",REPT(" ",99)),99)),5,8)

复制代码

手机号码的提取，右拉可以依次得到第1，2，3，……个号码

1. =TRIM(LEFT(SUBSTITUTE(MID(SUBSTITUTE("/"&$A2,"/1",REPT(" ",99)&1,COLUMN(A1)),99,99),"/",REPT(" ",99)),99))

复制代码

kane027

1. =TRIM(MID(REPLACE(SUBSTITUTE(" "&$A2,"/",REPT(" ",50)),FIND(" 0",SUBSTITUTE(" "&$A2&" 0","/",REPT(" ",50))),50,),COLUMN(A1)*50-49,50))

复制代码

电话号码分开的写法（右拉下拉）

kane027

区号分离-1.rar (64.39 KB, 下载次数: 11)

2016-5-28 21:51 上传

点击文件名下载附件

更新附件

沉默式、Kiss

谢谢大神们给了那么多的方法，按照你们的公式来用的话已经可以大大提高我的工作效率了，至于那些符号的只能要求客服下订单的时候不准出现那些乱七八糟的符号了，以后得多在这里泡着才能学到更多的东西增加我的工作效率，不然我自己在那儿折腾到死都弄不好

VBA万岁

沉默式、Kiss 发表于 2016-5-28 21:57
谢谢大神们给了那么多的方法，按照你们的公式来用的话已经可以大大提高我的工作效率了，至于那些符号的只能 ...

支持一下。

yuqiang462

去掉区号，固定电话提取出来还有什么意义？固定电话=IF(LEN(A2)=23,LEFT(A2,11),IF((LEN(A2)=11)*(LEFT(A2)="1"),"",MID(IF(LEFT(A2)<>"1",IF(COUNTIF(A2,"*/*"),LEFT(A2,FIND("/",A2)-1),A2)),IF(COUNT(FIND(LEFT(IF(LEFT(A2)<>"1",IF(COUNTIF(A2,"*/*"),LEFT(A2,FIND("/",A2)-1),A2)),3),"010,021,022,023,852,853")),4,5),10)))（区号是3位的有6个，你数据中有的是两个手机号，这公式会把第一个提取到固定，另一个放手机里）
手机=IF(LEFT(A2)="1",IF(COUNTIF(A2,"*/*"),LEFT(A2,11),A2),IF(COUNTIF(A2,"*/*"),MID(A2,FIND("/",A2)+1,11),""))

13284712166@

利用MID（）函数提取号段中第1，13，25位的数字，如果为0则为固定电话，否则为移动电话。再用IF、MID函数进行提取。

tcdatongye

通过vba做比较好