如何快速将EXCEL中许多手机号按移动和联通、电信自动分开，筛选出来

如何快速将EXCEL中许多手机号按移动和联通、电信自动分开，筛选出来

这里讲两种方法：

第一种就是正常的excel函数公式：

=IF(LEFT(A1,3)=“134”,“移动”,IF(LEFT(A1,3)=“135”,“移动”,IF(LEFT(A1,3)=“136”,“移动”,IF(LEFT(A1,3)=“137”,“移动”,IF(LEFT(A1,3)=“138”,“移动”,IF(LEFT(A1,3)=“139”,“移动”,IF(LEFT(A1,3)=“147”,“移动”,IF(LEFT(A1,3)=“150”,“移动”,IF(LEFT(A1,3)=“151”,“移动”,IF(LEFT(A1,3)=“152”,“移动”,IF(LEFT(A1,3)=“157”,“移动”,IF(LEFT(A1,3)=“158”,“移动”,IF(LEFT(A1,3)=“159”,“移动”,IF(LEFT(A1,3)=“182”,“移动”,IF(LEFT(A1,3)=“183”,“移动”,IF(LEFT(A1,3)=“187”,“移动”,IF(LEFT(A1,3)=“188”,“移动”,IF(LEFT(A1,3)=“178”,“移动”,IF(LEFT(A1,3)=“184”,“移动”,IF(LEFT(A1,3)=“198”,“移动”,IF(LEFT(A1,3)=“130”,“联通”,IF(LEFT(A1,3)=“131”,“联通”,IF(LEFT(A1,3)=“132”,“联通”,IF(LEFT(A1,3)=“155”,“联通”,IF(LEFT(A1,3)=“156”,“联通”,IF(LEFT(A1,3)=“185”,“联通”,IF(LEFT(A1,3)=“166”,“联通”,IF(LEFT(A1,3)=“175”,“联通”,IF(LEFT(A1,3)=“176”,“联通”,IF(LEFT(A1,3)=“186”,“联通”,IF(LEFT(A1,3)=“145”,“联通”,IF(LEFT(A1,3)=“133”,“电信”,IF(LEFT(A1,3)=“153”,“电信”,IF(LEFT(A1,3)=“180”,“电信”,IF(LEFT(A1,3)=“189”,“电信”,IF(LEFT(A1,3)=“181”,“电信”,IF(LEFT(A1,3)=“199”,“电信”,IF(LEFT(A1,3)=“173”,“联通”,IF(LEFT(A1,3)=“177”,“电信”,"")))))))))))))))))))))))))))))))))))))))

第二种方法就是利用代码来分开：

Sub test()
Dim d As Object
Dim r%, i%
Dim arr
Set d = CreateObject(“scripting.dictionary”)
yd = Array(134, 135, 136, 137, 138, 139, 150, 151, 152, 157, 158, 159, 182, 183, 184, 187, 188)
lt = Array(130, 131, 132, 155, 156, 186, 185)
dx = Array(133, 153, 189, 180, 181)
For i = 0 To UBound(yd)
d(yd(i)) = “移动”
Next
For i = 0 To UBound(lt)
d(lt(i)) = “联通”
Next
For i = 0 To UBound(dx)
d(dx(i)) = “电信”
Next
With Worksheets(“总的”)
r = .Cells(.Rows.Count, 1).End(xlUp).Row
arr = .Range(“a2:b” & r)
For i = 1 To UBound(arr)
xm = Val(Left(arr(i, 1), 3))
If d.Exists(xm) Then
arr(i, 2) = d(xm)
End If
Next
.Range(“b2”).Resize(UBound(arr), 1) = Application.Index(arr, 0, 2)
End With
End Sub

阅读完，记得反手一个“赞”呦~~~~