|
- Sub GetMobileNumber1()
- '---------------------------------------------------------------------------------------
- ' Procedure : GetMobileNumber1
- ' Author : hwc2ycy
- ' Date : 2013/3/15
- ' Purpose : 利用正则实现提取所有的手机号码
- '---------------------------------------------------------------------------------------
- '
- Dim lLastRow As Long, i As Long, j As Byte
- Dim arr, arr2()
- Dim strTemp As String
- Dim reg As Object, arrTemp
- lLastRow = Cells(Rows.Count, 2).End(xlUp).Row
- arr = Range("b1:c" & lLastRow)
- ReDim arr2(1 To UBound(arr), 1 To 1)
- Set reg = CreateObject("VBScript.regExp")
- With reg
- .Global = True
- .Pattern = "1[853]\d{9}"
- For i = LBound(arr) + 1 To UBound(arr)
- strTemp = Replace(arr(i, 1), " ", "")
- If .test(strTemp) Then
- Set arrTemp = .Execute(strTemp)
- strTemp = ""
- For j = 0 To arrTemp.Count - 1
- strTemp = strTemp & arrTemp(j) & vbCrLf
- Next
- arr2(i, 1) = Left(strTemp, Len(strTemp) - 1)
- End If
- Next
- End With
- Set reg = Nothing
- arr2(1, 1) = "手机号"
- Application.ScreenUpdating = False
- Range("e1").Resize(UBound(arr)) = arr2
- MsgBox "号码提取完成"
- Application.ScreenUpdating = True
- End Sub
- Sub GetMobileNumber2()
- '---------------------------------------------------------------------------------------
- ' Procedure : GetMobileNumber2
- ' Author : hwc2ycy
- ' Date : 2013/3/15
- ' Purpose : 利用正则实现提取第一个手机号码
- '---------------------------------------------------------------------------------------
- '
- Dim lLastRow As Long, i As Long, j As Byte
- Dim arr, arr2()
- Dim strTemp As String
- Dim reg As Object, arrTemp
- lLastRow = Cells(Rows.Count, 2).End(xlUp).Row
- arr = Range("b1:c" & lLastRow)
- ReDim arr2(1 To UBound(arr), 1 To 1)
- Set reg = CreateObject("VBScript.regExp")
- With reg
- .Global = True
- .Pattern = "1[853]\d{9}"
- For i = LBound(arr) + 1 To UBound(arr)
- strTemp = Replace(arr(i, 1), " ", "")
- If .test(strTemp) Then
- arr2(i, 1) = .Execute(strTemp)(0)
- End If
- Next
- End With
- Set reg = Nothing
- arr2(1, 1) = "手机号"
- Application.ScreenUpdating = False
- Range("e1").Resize(UBound(arr)) = arr2
- MsgBox "号码提取完成"
- Application.ScreenUpdating = True
- End Sub
复制代码 |
|