Find Phone Numbers

[dgr]

Hi,
I'm using Excel 2013. Using the code below, how do I search for any variation of the following phone numbers?
016-663 5143
011-23454512
019-2939 715
016 226 8878
0162333601
(6012) 276 3266
@0174072187

Set rngFind = .Find("the various formats of phone number", LookIn:=xlValues, lookat:= _
    xlPart, MatchCase:=True)

Thanks for your coding help. I appreciate it.

 

[GTO]

You would need to determine what the always enforceable rules are, in all of the string being evaluated.

 

[ParamRay]

.
.

Try a custom search procedure, like this one:

Sub FindNumberInRange()

    Dim rng As Range
    Dim num As String
    Dim i As Byte
    Dim temp1 As String
    Dim temp2 As String
    Dim cell As Range
    
    'Get range to search in
    On Error Resume Next
    Set rng = Application.InputBox( _
        Prompt:="Enter cells to search in:", _
        Type:=8)
    On Error GoTo 0
    
    'Exit if no range entered
    If rng Is Nothing Then Exit Sub
    
    'Get string to search for
    num = Application.InputBox( _
        Prompt:="Enter number to search for:", _
        Type:=2)
    
    'Exit if no string entered
    If num = "False" Then Exit Sub
    
    'Remove non-numeric characters from num
    temp1 = vbNullString
    For i = 1 To Len(num)
        If IsNumeric(Mid(num, i, 1)) Then
            temp1 = temp1 & Mid(num, i, 1)
        End If
    Next i
    
    'Exit if null string remains
    If Len(temp1) = 0 Then GoTo NoMatch
    
    'Loop through rng and look for a match
    For Each cell In rng
        temp2 = vbNullString
        For i = 1 To Len(cell.Value)
            If IsNumeric(Mid(cell.Value, i, 1)) Then
                temp2 = temp2 & Mid(cell.Value, i, 1)
            End If
        Next i
        If temp2 Like "*" & temp1 & "*" Then
            cell.Select
            MsgBox _
                Prompt:="First match is in cell " & cell.Address, _
                Buttons:=vbInformation
            Exit Sub
        End If
    Next cell
    
'If no match found
NoMatch:
    MsgBox _
        Prompt:="Not found.", _
        Buttons:=vbExclamation

End Sub