Excel判断手机号所属运营商的三种方法

创作时间:

作者:

@小白创作中心

Excel判断手机号所属运营商的三种方法

引用

来源

https://docs.pingcode.com/baike/4270773

在Excel中判断手机号所属运营商是一个常见的需求。本文将详细介绍三种主要方法：IF函数、VLOOKUP函数和正则表达式，帮助你快速准确地完成这一任务。

使用IF函数进行判断

IF函数是Excel中最常用的逻辑函数之一，通过简单的条件判断，可以快速识别手机号的运营商。

1. 了解手机号的前缀规则

首先，我们需要了解中国三大运营商（中国移动、中国联通、中国电信）的手机号前缀规则。以下是常见的手机号前缀：

中国移动：134、135、136、137、138、139、147、148、150、151、152、157、158、159、165、1703、1705、1706、178、182、183、184、187、188、198
中国联通：130、131、132、145、146、155、156、166、1704、1707、1708、1709、171、175、176、185、186
中国电信：133、149、153、173、174、177、180、181、189、191、199

2. 使用IF函数进行判断

在Excel中，假设手机号位于A列，我们可以在B列使用IF函数进行判断。以下是一个示例公式：

=IF(OR(LEFT(A1, 3)="134", LEFT(A1, 3)="135", LEFT(A1, 3)="136", LEFT(A1, 3)="137", LEFT(A1, 3)="138", LEFT(A1, 3)="139", LEFT(A1, 3)="147", LEFT(A1, 3)="148", LEFT(A1, 3)="150", LEFT(A1, 3)="151", LEFT(A1, 3)="152", LEFT(A1, 3)="157", LEFT(A1, 3)="158", LEFT(A1, 3)="159", LEFT(A1, 3)="165", LEFT(A1, 4)="1703", LEFT(A1, 4)="1705", LEFT(A1, 4)="1706", LEFT(A1, 3)="178", LEFT(A1, 3)="182", LEFT(A1, 3)="183", LEFT(A1, 3)="184", LEFT(A1, 3)="187", LEFT(A1, 3)="188", LEFT(A1, 3)="198"), "中国移动", IF(OR(LEFT(A1, 3)="130", LEFT(A1, 3)="131", LEFT(A1, 3)="132", LEFT(A1, 3)="145", LEFT(A1, 3)="146", LEFT(A1, 3)="155", LEFT(A1, 3)="156", LEFT(A1, 3)="166", LEFT(A1, 4)="1704", LEFT(A1, 4)="1707", LEFT(A1, 4)="1708", LEFT(A1, 4)="1709", LEFT(A1, 3)="171", LEFT(A1, 3)="175", LEFT(A1, 3)="176", LEFT(A1, 3)="185", LEFT(A1, 3)="186"), "中国联通", IF(OR(LEFT(A1, 3)="133", LEFT(A1, 3)="149", LEFT(A1, 3)="153", LEFT(A1, 3)="173", LEFT(A1, 3)="174", LEFT(A1, 3)="177", LEFT(A1, 3)="180", LEFT(A1, 3)="181", LEFT(A1, 3)="189", LEFT(A1, 3)="191", LEFT(A1, 3)="199"), "中国电信", "未知运营商")))

上述公式通过多重IF和OR函数结合LEFT函数，判断手机号的前三或四位数字，并返回相应的运营商名称。

使用VLOOKUP函数进行判断

VLOOKUP函数可以通过查找表来匹配数据，更加简洁和易于维护。

1. 创建运营商前缀表

首先，在Excel工作表中创建一个运营商前缀表，假设该表位于Sheet2中，A列为前缀，B列为对应的运营商。示例如下：

前缀	运营商
134	中国移动
135	中国移动
136	中国移动
130	中国联通
131	中国联通
133	中国电信
...	...

2. 使用VLOOKUP函数进行查找

在Sheet1中，假设手机号位于A列，我们可以在B列使用VLOOKUP函数进行查找。以下是一个示例公式：

=VLOOKUP(LEFT(A1, 3), Sheet2!$A$1:$B$100, 2, FALSE)

该公式通过LEFT函数提取手机号的前三位，然后在Sheet2的前缀表中查找对应的运营商名称。

使用正则表达式进行判断

Excel本身不直接支持正则表达式，但可以通过VBA（Visual Basic for Applications）脚本实现。以下是一个示例VBA脚本：

Function GetCarrier(phoneNumber As String) As String
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    '中国移动
    regex.Pattern = "^(134|135|136|137|138|139|147|148|150|151|152|157|158|159|165|1703|1705|1706|178|182|183|184|187|188|198)"
    If regex.Test(phoneNumber) Then
        GetCarrier = "中国移动"
        Exit Function
    End If
    '中国联通
    regex.Pattern = "^(130|131|132|145|146|155|156|166|1704|1707|1708|1709|171|175|176|185|186)"
    If regex.Test(phoneNumber) Then
        GetCarrier = "中国联通"
        Exit Function
    End If
    '中国电信
    regex.Pattern = "^(133|149|153|173|174|177|180|181|189|191|199)"
    If regex.Test(phoneNumber) Then
        GetCarrier = "中国电信"
        Exit Function
    End If
    '未知运营商
    GetCarrier = "未知运营商"
End Function

将上述代码复制到Excel VBA编辑器中，然后在工作表中使用该自定义函数：