I need to be able to eliminate all cells in a column that don't contain a telephone number in the cell. Note: when a telephone number would be in a cell,it would be the only data within that cell. Does anyone have an idea on how this can be done?
When you select a cell with a telephone number in it, what do you see in the Formula Bar (for the above example)... 2125551212 or (212) 555-1212?(212) 555-1212
The format of the tel numbers will always be (xxx) yyy-zzzz
Okay, give this macro a try...Rick,
it would be (212) 555-1212
Code:
|
Excel 2007 | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | Name | (212) 555-1212 | |||
2 | address | (949) 444-3424 | |||
3 | birthday | (301) 555-6666 | |||
4 | (212) 555-1212 | ||||
5 | name | ||||
6 | address | ||||
7 | where we met | ||||
8 | (949) 444-3424 | ||||
9 | favorite restaurant | ||||
10 | name | ||||
11 | birthday | ||||
12 | address | ||||
13 | (301) 555-6666 | ||||
14 | remember his gift | ||||
15 | |||||
Sheet1 |
Sub ExtractPhoneNumbers()
' hiker95, 08/13/2015, ME875549
Dim a As Variant, i As Long
Dim o As Variant, j As Long, n As Long
Application.ScreenUpdating = False
a = Range("A1", Cells(Rows.Count, "A").End(xlUp))
n = Application.CountIf(Range("A1", Cells(Rows.Count, "A").End(xlUp)), "*)*")
ReDim o(1 To n, 1 To 1)
For i = 1 To UBound(a, 1)
If a(i, 1) Like ["(###) ###-####"] Then
j = j + 1: o(j, 1) = a(i, 1)
End If
Next i
Range("C1").Resize(UBound(a, 1)).ClearContents
Range("C1").Resize(UBound(o, 1), UBound(o, 2)) = o
Columns(3).AutoFit
Application.ScreenUpdating = True
End Sub