如何快速将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

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