Removing alpha characters from a string with multiple telephone numbers

[JKDSki]

I have a spreadsheet that was created from a database export. One of the fields/cells contains multiple telephone numbers and alpha descriptors. The formatting is not consistent and there can be 1 phone number or 15 listed. Most multiple numbers are separated by a carriage return, rather than a space. The good thing is that all the phone numbers have the same formatting (xxx-xxx-xxxx) which I'm hoping will make it easier.

I would like to learn how to delete the alpha characters and be left with phone numbers separated with ANY character.
Once that is completed, I will attempt to use one of the other formulas I found on this site (awesome) to split the phone numbers into separate rows.

I've attached a sample spreadsheet (At least I hope it is - new to the forum!). The Column I'm looking at is the CKT NO

Thank you so much!

Janet

[jindon]

Try

Sub test()
    Dim a, i As Long, m As Object, txt1 As String, txt2 As String
    With Range("d1", Range("d" & Rows.Count).End(xlUp)).Resize(, 2)
        a = .Value
        With CreateObject("VBScript.RegExp")
            .Global = True: .MultiLine = True
            .Pattern = "^((\d{3}-){2}\d{4})\b *(.*)$"
            For i = 2 To UBound(a, 1)
                For Each m In .Execute(a(i, 1))
                    txt1 = txt1 & IIf(txt1 <> "", vbLf, "") & m.submatches(0)
                    txt2 = txt2 & IIf(txt2 <> "", vbLf, "") & m.submatches(2)
                Next
                a(i, 1) = txt1: txt1 = ""
                a(i, 2) = txt2: txt2 = ""
            Next
        End With
        .Value = a
    End With
End Sub

[JKDSki]

This worked perfect! I don't think I could have ever figured this one out. I have some programming background, but not at this level. I'm planning on studying this one to really understand how it works!
Thanks so much!

[jindon]

You are welcome and thanks for the rep.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

If you want to understand the code you will need to google with the key words like

VBA Regular Expression