Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Eliminate all cells that don't contain a telephone number

  1. #1
    Board Regular
    Join Date
    Aug 2014
    Posts
    374
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Eliminate all cells that don't contain a telephone number

    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?

  2. #2
    New Member
    Join Date
    Apr 2015
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Eliminate all cells that don't contain a telephone number

    hi

    so, imagine that the telephone number has 9 numbers.

    if the cell only have 8 numbers will be deleted ?

  3. #3
    Board Regular
    Join Date
    Dec 2012
    Location
    South Floriduhhh
    Posts
    229
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Eliminate all cells that don't contain a telephone number

    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 by brucef2112; Aug 13th, 2015 at 09:48 PM.

  4. #4
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    17,519
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Eliminate all cells that don't contain a telephone number

    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 by hiker95; Aug 13th, 2015 at 09:48 PM.
    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

  5. #5
    Board Regular
    Join Date
    Aug 2014
    Posts
    374
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Eliminate all cells that don't contain a telephone number

    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.

  6. #6
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    32,517
    Post Thanks / Like
    Mentioned
    52 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Eliminate all cells that don't contain a telephone number

    Quote Originally Posted by skyport View Post
    (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?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  7. #7
    Board Regular
    Join Date
    Aug 2014
    Posts
    374
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Eliminate all cells that don't contain a telephone number

    Rick,

    it would be (212) 555-1212

  8. #8
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    32,517
    Post Thanks / Like
    Mentioned
    52 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Eliminate all cells that don't contain a telephone number

    Quote Originally Posted by skyport View Post
    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
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  9. #9
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    17,519
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Eliminate all cells that don't contain a telephone number

    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.
    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

  10. #10
    Board Regular
    Join Date
    Aug 2014
    Posts
    374
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Eliminate all cells that don't contain a telephone number

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •