Eliminate all cells that don't contain a telephone number

skyport

Active Member
Joined
Aug 3, 2014
Messages
374
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?
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
hi

so, imagine that the telephone number has 9 numbers.

if the cell only have 8 numbers will be deleted ?
 
Upvote 0
Do you mean you want to delete the rows without a phone number?
If so, select all the columns used in your worksheet (with mouse, drag across A,B,C,.....)
Then on the menu bar you can go to Data > Sort. Select the column with the phone number, then click order by: Descending
All rows with blank phone numbers will be at the top. you can select and delete.

OR
Do you mean you have lots of data and you just want to filter the data so you only see the rows with a phone number?
Select all the columns used in your worksheet (with mouse, drag across A,B,C,.....)
On the menu bar select Data > Filter > AutoFilter.
You'll now see a small button in each cell on row 1.
click the button on your column with the phone number.
At the bottom of the list select (NonBlanks). the column will now show only cells that have a phone number (or anything not blank)
To restore to see all rows again, click the button and scroll to top and select (All)

There is also a (Custom...) choice which would allow you to filter for say all phone numbers in a particular area code.

 
Last edited:
Upvote 0
skyport,

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?

3. What column contains the telephone numbers?

4. What is the first cell in the column that can contain a telephone number?

5. Can you provide examples of the telephone numbers?

6. Can you provide examples of what can be in the cells if they are not telephone numbers?

7. If a cell does not contain a telephone number, what should we do?

7a. Delete what is in the cell?

7b. Delete the row?
 
Last edited:
Upvote 0
Thanks everyone for helping me with this. Let me answer everyone'e questions and suggestions and clarify a bit.

There would only be one column used on the sheet which would contain a MIX of names, addresses, personal notes and telephone numbers for individuals in a personal directory.

As an example, a small part of the column might look as follows with each line of data below being a separate cell in Column A lines 1-14:


Name
address
birthday
(212) 555-1212
name
address
where we met
(949) 444-3424
favorite restaurant
name
birthday
address
(301) 555-6666
remember his gift

The column in reality might be many more entries but all following the same type of info.

A key point is the telephone number would ALWAYS be on a line and cell by itself.

The desire is to either delete everything except the telephone numbers or have the telephone numbers all end up in a separate column, or could be at the top of the column if a sort is required, whichever is easier.

The format of the tel numbers will always be (xxx) yyy-zzzz

A PC is being used with Windows 7 and excel 2000.
 
Upvote 0
(212) 555-1212

The format of the tel numbers will always be (xxx) yyy-zzzz
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?
 
Upvote 0
Rick,

it would be (212) 555-1212
Okay, give this macro a try...
Code:
Sub LeaveOnlyPhoneNumbers()
  Dim Addr As String
  Addr = "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row
  Range(Addr) = Evaluate(Replace("IF(LEFT(@)=""("",IF(@="""","""",@),NA())", "@", Addr))
  On Error GoTo NoPhoneNumbers
  Columns("A").SpecialCells(xlConstants, xlErrors).Delete xlShiftUp
NoPhoneNumbers:
End Sub
 
Upvote 0
skyport,

Here is a macro solution for you to consider that uses two arrays in memory, and, will adjust to the varying number of raw data rows in column A.

Sample raw data, and, results:


Excel 2007
ABC
1Name(212) 555-1212
2address(949) 444-3424
3birthday(301) 555-6666
4(212) 555-1212
5name
6address
7where we met
8(949) 444-3424
9favorite restaurant
10name
11birthday
12address
13(301) 555-6666
14remember his gift
15
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:
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

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ExtractPhoneNumbers macro.
 
Upvote 0
To Both Rick and Hiker 95

Both programs work absolutely perfect. There is even a purpose for using them both because of the way each treats the data in the end result.

Thank you both so very much. This is what makes the Excel community so great. It was great to see you both again here on the forum and I also thank both of you for all the great solutions you have contributed over the past year.

If anyone else started a solution and you want me to test it out, I will be happy to give feed back although the problem has been resolved.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top